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
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
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>
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('programmer');<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('programmer');ERROR 1364 (HY000): Field 'name' doesn't have a default value</span>
Execution failed, prompting that the field name cannot be a null value
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('','fdipzone','programmer');<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>
Executed in strict mode<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(id,name,content) value('','fdipzone','programmer');<br/>ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1</span>
<span style="color: rgb(0, 0, 0);">mysql> insert into mytable(id,name,content) value(null,'fdipzone','programmer');<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>
##3. Text field default value test
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 '', -> 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>
Execution in strict mode
## 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 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!<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 '', -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br/>ERROR 1101 (42000): BLOB/TEXT column 'content' can't have a default value</span>
Does this article explain the mysql strict mode Strict Mode? For more related recommendations, please pay attention to the php Chinese website.