An explanation of mysql strict mode Strict Mode-Mysql Tutorial-php.cn

An explanation of mysql strict mode Strict Mode

jacklove

Release： 2018-06-09 09:47:44

Original

2207 people have browsed it

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>

Copy after login

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>

Copy after login

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>

Copy after login

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>

Copy after login

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>

Copy after login

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>

Copy after login

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>

Copy after login

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>

Copy after login

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!