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
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;
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)
Execute in strict mode
mysql> insert into mytable(content) values('programmer'); ERROR 1364 (HY000): Field 'name' doesn't have a default value
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 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
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)
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 | +------------------------------+
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
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!