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 under the MySQL installation folder

Adding strict_trans_tables in Sql_mode means that strict mode is turned on. If no increase is not strict mode, after the change, restart MySQL can

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. Examples:

Create a data table for easy testing

CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `content` text NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.not NULL field insert NULL value test

Insert a record, name has a value of NULL

Run in non-strict mode

mysql> insert into mytable(content) values(‘programmer‘);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from mytable;+----+------+------------+| id | name | content    |+----+------+------------+|  1 |      | programmer |+----+------+------------+1 row in set (0.00 sec)

Run successfully, the value of name is actively converted to "

Run in strict mode

into mytable(content) values(‘programmer‘);ERROR1364‘name‘ doesn‘t have a default value

Failed to run, prompt field name cannot be a null value

2. Self-growth field insert "value test

Insert "Value for ID field"

Run in non-strict mode

mysql> insert into mytable(id,name,content) value(‘‘,‘fdipzone‘,‘programmer‘);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from mytable;+----+----------+------------+| id | name     | content    |+----+----------+------------+|  1 | fdipzone | programmer |+----+----------+------------+1 row in set (0.00 sec)

Run successfully

Run in strict mode

intovalue(‘‘,‘fdipzone‘,‘programmer‘1366value‘‘for‘id‘1

Failed to run. The prompt field ID cannot be "

mysql> insert into mytable(id,name,content) value(null,‘fdipzone‘,‘programmer‘);Query OK, 1 row affected (0.00 sec)mysql> select * from mytable;+----+----------+------------+| id | name     | content    |+----+----------+------------+|  1 | fdipzone | programmer |+----+----------+------------+1 row in set (0.00 sec)

The field ID is null to run successfully

3.text Field Default value Test

Create a data table mytable. The text setting default value default= "

Run in non-strict mode

`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;+------------------------------+| Tables_in_test_version       |+------------------------------+| mytable                      |+------------------------------+

Run successfully

Run in strict mode

Mysql>CREATE TABLE' MyTable '( -' id 'int One) not NULLAuto_increment, -' name 'varchar -) not NULL, -' content 'Text not NULLDefault"', -PRIMARY KEY (' id ') -) ENGINE=InnoDB DEFAULT CHARSET=UTF8; ERROR1101(42000): Blob/text column' content 'Can' t have a default value

The run failed, prompting the content field to be of type text and cannot use the default value.

Summarize. 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