Detailed explanation of MySQL Strict Mode knowledge points

I. Strict Mode Explanation

According to the restrictions of strict mode (STRICT_TRANS_TABLES) in MySQL 5.0 and above:

1). Does not support inserting null values ​​into not null fields

2). It does not support inserting '' value into the auto-increment field, but null value can be inserted

3). Does not support default values ​​for text fields

Look at the following code: (the first field is an auto-increment field)

Sql code

$query="insert into demo values('','$firstname','$lastname','$sex')";

The above code is only valid in non-strict mode.

Code

$query="insert into demo values(NULL,'$firstname','$lastname','$sex')";

The above code is only valid in strict mode. Replace the empty value '' with NULL.

II. Make the database support Strict Mode

1. Make the following improvements to the database structure to support strict mode:

1) Set a non-null default value for all not null fields. The default value for strings is '', the default value for numbers is 0, and the default value for dates is '0000-00-00 00:00:00'

2) Remove the default value of the text field

3) Standardization improvements: Change the title field to varchar(255) and change the null field with default value to not null field

2. If the installed PHP program database structure turns off Strict mode

1). One is to remove strict mode when installing MySQL 5.0 (or above).

Edit my.cnf and turn off Strict Mode:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2). Another way is to modify the query statement. For example,

if ($this->dbcharset) {
@mysql_query("SET NAMES ".$this->dbcharset);
}

Execute later

mysql_query("SET @@sql_mode = ''");

Make sure you are using MySQL 5.

The mysqli method is similar, that is, the execution is

mysqli_query($this->connection_id, "SET @@sql_mode = ''");

This is the end of this article about the detailed explanation of MySQL Strict Mode knowledge points. For more relevant MySQL Strict Mode content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!