Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql strict mode Strict Mode handout, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

1. Turn the Strict Mode method on and off

Locate the my.cnf file under the mysql installation directory (my.ini for windows systems)

Add STRICT_TRANS_TABLES to sql_mode to enable strict mode. If not, 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 feature description

Inserting null values into not null fields is not supported

Inserting values for self-growing fields is not supported

Default values for text fields are not supported

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 insert null value test

Insert a record with the value of name 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)

If the execution succeeds, 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

Execution failed, prompt field name cannot be null

two。 Self-growing field insertion value test

Insert a value for the id field

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

Successful execution

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

If field id is null, it can be executed successfully.

3.text field default value test

Create a data table mytable, where text sets the default value default= "

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

+-+

Successful execution

Execute 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

Execution failed, indicating that the content field is of type TEXT and cannot use the default value.

To sum up, the use of mysql strict mode can make the data more secure and strict, but the disadvantage is to reduce the compatibility of empty data into the repository. It is recommended that the development environment use strict patterns to improve the quality of code and the rigor of data.

Does this article explain the strict mode Strict Mode of mysql? please pay attention to the relevant recommendations.

Related recommendations:

Explanation of the problems that beginners are easy to ignore when php uses explode to segment strings

Explanation of the method of two columns of data in mysql interchange table

How to generate 0,1 Random Decimal number by php

The above is the details of the explanation of mysql strict mode Strict Mode, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.