MySQL's Sql_mode

(a) Basic introduction

Set Sql_mode= "", that is, forced not to set the MySQL mode (such as no input detection, error prompts, grammar mode check, etc.) should be able to improve performance, but the following problems:

If inappropriate data is inserted (error type or paranormal), MySQL will set the data to "best possible data" without error, such as:

/number set to: 0/possible minimum/maximum possible value

/string set to: empty string/maximum capacity strings that can be stored

/Expression Set to: Returns an available value (1/0-null)

So, the workaround is: All columns should have default values, which is good for performance.

Of course, if you particularly like SQL Server mode, you can also set it to sql_mod= "SQL Server" (as if it were written), so that MySQL works as SQL Server, not recommended.

(ii) Common ways

MySQL can run under different SQL mode modes, and SQL mode defines the SQL syntax, data validation, and so on that MySQL should support!

To view the default SQL mode:

SELECT @ @sql_mode;

My database is:

Strict_trans_tables,no_auto_create_user,no_engine_substitution

In this mode, if the length of the inserted data is greater than the length of the definition, then an error will be shown!

Set session sql_mode= ' Real_as_float,pipes_as_concat,ansi_quotes,ignore_space,ansi ';

In this mode: when the length of the inserted data is greater than the definition, it is intercepted and warned, but can be inserted in

Session indicates only valid in this session

Global: Indicates that this connection does not take effect and is valid for a new connection

Enable the No_backslash_escapes mode to make the backslash a normal character, enabling this mode is a good choice when importing data with backslashes in the data

Enable Pipes_as_cncat mode, will | | As a normal string

SQL Mode Value Description

Ansi

' Real_as_float,pipes_as_concat,ansi_quotes,ignore_space and ANSI combination ',

This pattern makes syntax and behavior more compliant with the standard SQL

Strict_trans_tables use with transactional and non-transactional tables, strict mode

Traditional is also a strict mode, giving an error instead of a warning for inserting an incorrect value. Roll back as soon as an error occurs when using a transaction

(iii) Modification of Sql_mode to solve problems

Today installed a mysql5.0, took a long time before the online program to check if it is working properly.

found that the previous program actually does not work properly, the prompt information is as follows:

Database error:invalid Sql:insert into Survey_userhistory (userid,jobid,type,action,starttime,endtime) VALUES (' 17′, ' , ' User ', ' Login ', ', ' 2008-11-23 14:33:56′)

MySQL error:1366 (Incorrect integer value: "For column ' JobId ' at row 1)

Session halted.

The first reaction was related to mysql5.0 's new Sql-mode, which had just read the handbook a while back. Sure enough, after making the following changes, no longer prompt.

Modify the My.ini file.

# Set The SQL mode to strictsql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution"

Switch

# Set The SQL mode to strictsql-mode= "No_auto_create_user,no_engine_substitution"

MySQL's Sql_mode