How to use mysql strict mode Strict Mode

1. How to turn on and off Strict Mode

Find the my.cnf (my.ini for Windows system) file in the mysql installation directory

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

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

• Does not support inserting "value" into self-increasing fields

• Does not support default values ​​for text fields

3. Example ：

Create a data table to facilitate 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 inserts null value test

Insert a record, the value of name is null

Execute 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)

Execution is successful, the value of name is automatically converted to "

Execute in strict mode

mysql> insert into mytable(content) values('programmer');
ERROR 1364 (HY000): Field 'name' doesn't have a default value

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

2. Insert "value test for auto-increment field"

Insert "value for id field

Executed 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)

Executed successfully

Executed in strict mode

mysql> insert into mytable(id,name,content) value('','fdipzone','programmer');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

Execution failed, prompting that the 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)

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

3. Text field default value test

Create one Data table mytable, where text sets the default value default=”

Executed in non-strict mode

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

Executed successfully

Execution in strict mode

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;
ERROR 1101 (42000): BLOB/TEXT column 'content' can't have a default value

Failed to execute, prompting that the content field is of type TEXT and the default value cannot be used.

Using MySQL's strict mode can enhance data security, but the disadvantage is that it reduces the compatibility of empty data into the database. It is recommended that the development environment use strict mode to improve code quality and data rigor.

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