An explanation of mysql strict mode Strict Mode

1. How to turn on and off Strict Mode

Find my.cnf in the mysql installation directory (for Windows systems, it is my.ini) File

Adding STRICT_TRANS_TABLES to sql_mode means turning on strict mode. If not added, it means non-strict mode. After modification, restart mysql. But

For example, this means that strict mode is turned on:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2. Strict Mode Function Description

• Does not support inserting null values ​​into not null fields

• No Supports inserting "value" into self-increasing fields

• Does not support text fields with default values

3. Example:

Create a data table to facilitate testing

<span style="color: rgb(0, 0, 0);">CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `content` text NOT NULL, PRIMARY KEY (`id`)<br/>) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span>

1.Not null field inserts null value test

Insert a record, the value of name is null

Execute in non-strict mode

<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(content) values(&#39;programmer&#39;);<br/>Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from mytable;<br/>+----+------+------------+| id | name | content    |<br/>+----+------+------------+|  1 |      | programmer |<br/>+----+------+------------+1 row in set (0.00 sec)</span>

The execution is successful, and the value of name is automatically converted to "

Execute in strict mode##

<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(content) values(&#39;programmer&#39;);ERROR 1364 (HY000): Field &#39;name&#39; doesn&#39;t have a default value</span>

Execution failed, prompting that the field name cannot be a null value

2. Insert "value test for self-increasing fields

Insert "value for id field

Execute in non-strict mode##

<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(id,name,content) value(&#39;&#39;,&#39;fdipzone&#39;,&#39;programmer&#39;);<br/>Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from mytable;<br/>+----+----------+------------+| id | name     | content    |<br/>+----+----------+------------+|  1 | fdipzone | programmer |<br/>+----+----------+------------+1 row in set (0.00 sec)</span>

Execution successful

Executed in strict mode

<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(id,name,content) value(&#39;&#39;,&#39;fdipzone&#39;,&#39;programmer&#39;);<br/>ERROR 1366 (HY000): Incorrect integer value: &#39;&#39; for column &#39;id&#39; at row 1</span>

The execution fails and the prompt field id cannot be "

<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(id,name,content) value(null,&#39;fdipzone&#39;,&#39;programmer&#39;);<br/>Query OK, 1 row affected (0.00 sec)mysql> select * from mytable;<br/>+----+----------+------------+| id | name     | content    |<br/>+----+----------+------------+|  1 | fdipzone | programmer |<br/>+----+----------+------------+1 row in set (0.00 sec)</span>

If the field id is null, the execution can be successful

##3. Text field default value test

#Create a data table mytable, in which text sets the default value default=”

Executed in non-strict mode##

<span style="color: rgb(0, 0, 0);">mysql> CREATE TABLE `mytable` (    ->  `id` int(11) NOT NULL AUTO_INCREMENT,    ->  `name` varchar(20) NOT NULL,    ->  `content` text NOT NULL default &#39;&#39;,    ->  PRIMARY KEY (`id`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> show tables;<br/>+------------------------------+| Tables_in_test_version       |<br/>+------------------------------+| mytable                      |<br/>+------------------------------+</span>

Executed successfully

Execution in strict mode

##

<span style="color: rgb(0, 0, 0);">mysql> CREATE TABLE `mytable` (    ->  `id` int(11) NOT NULL AUTO_INCREMENT,    ->  `name` varchar(20) NOT NULL,    ->  `content` text NOT NULL default &#39;&#39;,    ->  PRIMARY KEY (`id`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br/>ERROR 1101 (42000): BLOB/TEXT column &#39;content&#39; can&#39;t have a default value</span>

Execution failed , prompting that the content field is of type TEXT and the default value cannot be used. Summary, using mysql strict mode can make the data more secure and strict, but the disadvantage is that it reduces the compatibility of empty data in the database. It is recommended that the development environment use strict mode to improve code quality and data rigor.

Does this article explain the mysql strict mode Strict Mode? For more related recommendations, please pay attention to the php Chinese website.

Related recommendations:

Explanation of issues that novices can easily overlook using explode to split strings in php

##Explanation on the two column data method in mysql exchange table

##How to generate 0~1 random decimals through php

The above is the detailed content of An explanation of mysql strict mode Strict Mode. For more information, please follow other related articles on the PHP Chinese website!