MySQL Strict mode Strict the mode description

1. Open and close the strict mode method

Locate the my.cnf(the Windows system is the My.ini) file in the MySQL installation directory

Adding strict_trans_tables in sql_mode means that strict mode is turned on, and if not joined, it means non-strict mode, then restart MySQL after modification.

This means, for example, that strict mode is turned on:
Sql_mode=no_engine_substitution,strict_trans_tables

2.Strict Mode function Description

• Inserting null values on NOT NULL fields is not supported
• Inserting "value to self-growing field" is not supported
• The text field is not supported with a default value

3. Example:

Create a data table for easy testing

CREATE TABLE' mytable ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar( -) not NULL, ' content 'text  not NULL, PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

1.not NULL field insert NULL value test

Insert a record, name has a value of NULL

Execute in non-strict mode

Mysql> Insert  intoMyTable (content)Values('Programmer'); Query OK,1Row affected,1Warning (0.00sec) MySQL> Select *  frommytable;+----+------+------------+|Id|Name|Content|+----+------+------------+|  1 |      |Programmer|+----+------+------------+1Rowinch Set(0.00Sec

Execution succeeds, the value of name automatically transitions to "

Execute in Strict mode

MySQL>insertintovalues('programmer'   1364'name' doesn'T has a default value

Execution failed with prompt field name cannot be a null value

2. Self-growing field insert "value test

Insert "Value for ID field"

Execute in non-strict mode

Mysql> Insert  intoMyTable (id,name,content) value ("','Fdipzone','Programmer'); Query OK,1Row affected,1Warning (0.00sec) MySQL> Select *  frommytable;+----+----------+------------+|Id|Name|Content|+----+----------+------------+|  1 |Fdipzone|Programmer|+----+----------+------------+1Rowinch Set(0.00Sec

Successful execution

Execute in strict mode

MySQL>insert into MyTable (id,name,content) value (','fdipzone  ','programmer'1366integer'  forcolumn'ID'1

Execution failed with the prompt field ID cannot be "

Mysql> Insert  intoMyTable (id,name,content) value (NULL,'Fdipzone','Programmer'); Query OK,1Row affected (0.00sec) MySQL> Select *  frommytable;+----+----------+------------+|Id|Name|Content|+----+----------+------------+|  1 |Fdipzone|Programmer|+----+----------+------------+1Rowinch Set(0.00Sec

The field ID is null to execute successfully

3.text Field Default value Test

Create a data table MyTable, where text sets the default value default= "

Execute in non-strict mode

Mysql> CREATE TABLE' MyTable ' ( -' ID 'int( One) not NULLAuto_increment, -' Name 'varchar( -) not NULL,     -' Content 'text  not NULL default "',     -  PRIMARY KEY(' id ') -) ENGINE=InnoDBDEFAULTCHARSET=UTF8; Query OK,0Rows affected,1Warning (0.03sec) MySQL>show tables;+------------------------------+|Tables_in_test_version|+------------------------------+|MyTable|+------------------------------+

Successful execution

Execute in Strict mode

Mysql> CREATE TABLE' MyTable ' ( -' ID 'int( One) not NULLAuto_increment, -' Name 'varchar( -) not NULL,     -' Content 'text  not NULL default "',     -  PRIMARY KEY(' id ') -) ENGINE=InnoDBDEFAULTCHARSET=UTF8; ERROR1101(42000): BLOB/TEXT column 'content'Can'T has a default value

Execution failed with a hint that the content field is of type text and cannot use the default value.


In summary, the use of MySQL strict mode can make data more secure and strict, the disadvantage is to reduce the storage of empty data compatibility. It is recommended that the development environment use strict mode to improve the quality of the code and the rigor of the data.

MySQL Strict mode Strict the mode description