MySQL SQL mode setting (sql_mode system variable)

One, SQL mode introduction

  • The SQL mode of the MySQL server has several effects on the execution of SQL statements

sql_mode system variable

  • The SQL mode of the server. This variable will change certain behaviors of the MySQL server, making it more compliant with the SQL language standard or compatible with other database servers or older versions of MySQL servers. The value of this variable should be an empty string (this will clear the previously set SQL mode) or a series of values ​​separated by commas from one or more mode values ​​described below.
  • Since MySQ 5.6.6, its default value is NO_ENGINE_SUBSTITUTION; for previous versions, its value is an empty string. Some mode values ​​are so simple that they can be used individually to enable a certain behavior. The others are compound modes, each compound SQL mode covers a variety of simple SQL modes, which makes it easy for users to set multiple SQL modes at once.
  • sql_mode has different values, which affect the behavior of the client
  • sql_mode can be set to global, which has an impact on all clients; it can also be restricted for clients

Two, sql_mode value

  • Some common SQL mode values ​​are listed below:
  • STRICT_ALL_TABLES, STRICT_TRANS_TABLES: Enable "strict" mode. In this mode, the MySQL server will treat the received bad data more strictly. (Specifically, it will not convert these bad data values ​​into the closest valid value, but refuse to accept them).
  • TRADITIONAL: Combination mode. It is similar to strict mode, but it also enables several other modes, which introduce more additional restrictions to achieve more stringent data checks. The TRADITIONAL mode allows the MySQL server to behave closer to those traditional SQL servers when dealing with bad data.
  • ANSI_QUOTES: This mode tells the server to recognize double quotes as an identifier quote character.
  • PIPES_AS_CONCAT: In this mode, the server will treat "||" as a standard SQL string concatenation operator, instead of treating it as a synonym for the OR operator.
  • ANSI: It is also a combination mode. It will enable ANSI_QUOTES, PIPES_AS_CONCAT and several other mode values ​​at the same time. As a result, the behavior of the MySQL server is closer to standard SQL than its default operating state.
  • The following figure lists the compound SQL mode and the mode content contained in each compound mode:
  • It is called TRANITIONAL mode because it enables such modes-they enable MySQL to behave like traditional databases that reject invalid data when processing input values. It is a bit like strict mode, but contains several additional constraints for stricter checks.

Three, sql_mode settings

  • Precautions for setting sql_mode:
  • When setting the SQL mode, you can specify a mode value or multiple mode values ​​(separated by a comma).
  • When an empty string is assigned to sqlo_mode, it means that the mode value is cleared.
  • The mode value is not case sensitive.

Global Settings

  • You can set sql_mode when starting mysql . E.g:
mysqld --sql_mode="TRADITIONAL"mysqld --sql_mode=ANSI_QUOTES,PIPES_AS_CONCAT""
  • Mysql may also be in the configuration file configuration
  • Or after entering mysql, set the global sql_mode through the GLOBAL keyword . Setting up global variables requires SUPER management authority. After the setting is completed, the configuration will be used when connecting to the client. E.g:

For single session settings

  • You can set sql_mode in the session , then the sql_mode is only valid for the client, and it becomes invalid after the session ends
  • E.g:

Four, view

  • For example, to view the SQL mode value of the current session :
SELECT @@SESSION.sql_mode;
  • For example, to view the global SQL mode value:
SELECT @@GLOBAL.sql_mode;