How to turn off strict mode in mysql

How to turn off mysql strict mode: 1. Open and edit the mysql configuration file "my.cnf"; 2. Search for the "sql-mode" keyword and comment out the relevant statements of "sql-mode"; 3. Use the "service mysqld restart" command to restart mysql.

The operating environment of this tutorial: linux5.9.8 system, mysql8 version, Dell G3 computer.

Check whether Mysql has strict mode enabled:

Open the MySQL configuration file my.cnf (my.ini for Windows).

Search sql-mode If it cannot be searched, it means non-strict mode.

If found, it means strict mode is turned on, for example:

sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Turn on and off strict mode

##Turn on Strict mode:

vi /etc/my.cnf #编辑mysql配置文件

搜索sql-mode关键字，若没有，在文件尾添加一行
替换成：
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

service mysqld restart 重启mysql

Turn off strict mode:

vi /etc/my.cnf #编辑mysql配置文件

搜索sql-mode关键字。注释sql-mode相关语句
#sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

service mysqld restart 重启mysql

Extended knowledge: Comparison between strict mode and non-strict mode (relaxed mode)

Does not support inserting null values ​​into not null fields
• Does not support inserting "value" into self-increasing fields
• Does not support text fields Default value

Example:

CREATE TABLE `test_table` (
 `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, the value of name is null

Execute in non-strict mode

mysql> insert into test_table(content) values('51ask');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test_table;
+----+------+------------+
| id | name | content    |
+----+------+------------+
|  1 |      | 51ask      |
+----+------+------------+
1 row in set (0.00 sec)

#执行成功

Execute in strict mode

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

#执行失败，提示字段name不能为null值

The above three points will no longer be tested one by one.

It can be seen that using mysql strict mode can make the data more secure and strict. 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 the quality of the code and the rigor of the data. .

Related recommendations:

mysql video tutorial, "Linux tutorial"

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