Shulou(Shulou.com)05/31 Report--

This article shows you what the SQL mode of MySQL server is, which is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Unlike other databases, MySQL servers can run in different SQL modes and can be applied in different ways for different clients, depending on the value of the sql_mode system variable.

The SQL schema defines the SQL syntax and data validation (data validation checking) supported by MySQL databases, making it easier to use MySQL in different environments.

In MySQL, the SQL pattern is often used to solve the following types of problems:

By setting SQL Mode, different strict degrees of data verification can be completed, which effectively ensures the accuracy of the data.

By setting SQL Mode to ANSI mode, you can ensure that most SQL conforms to the standard SQL syntax, so that there is no need for major modifications when migrating between different databases.

Before data migration between different databases, setting up SQL Mode makes it easier to migrate data from MySQL to the target database.

Common values of sql_mode system variabl

Several common values for SQL modes are listed below.

TRICT_ ALL_TABLES and STRICT_ TRANS_TABLES

If you set the value of sql_mode to TRICT_ALL_TABLES and STRICT_TRANS_TABLES, MySQL will enable strict mode. In strict mode, the MySQL server will be more strict with the non-conforming data received, it will not convert these nonconforming data into the nearest valid value, but will refuse to accept them.

To put it simply, the strict mode of MySQL is the strict checking of data by MySQL itself, such as format, length, type, and so on.

TRADITIONAL

Similar to strict mode, but gives an error rather than a warning for inserted nonconforming values. It can be applied to transactional and non-transactional tables, and when used in transactional tables, it will be rolled back as soon as an error occurs.

If you are using a non-transactional storage engine, it is recommended that you do not set the SQL Mode value to TRADITIONAL, because the operation performed before the error will not be rolled back, which will result in only part of the operation.

ANSI_QUOTES

The MySQL server recognizes the double quotation marks as an identifier reference character rather than the quote character of a string. So when you enable ANSI_QUOTES, you cannot refer to a string in double quotes.

PIPES_ AS_ CONCAT

Causes the MySQL server to treat | as a standard SQL string concatenation operator rather than as a synonym for the OR operator.

In databases such as Oracle, | | is regarded as a concatenation operator of a string, so SQL with the | | operator in other databases cannot be executed in MySQL. To solve this problem, MySQL provides this value.

ANSI

ANSI_QUOTES, PIPES_ AS_CONCAT, and several other schema values are enabled at the same time, making the MySQL server behave closer to the standard SQL than its default running state.

How to set up sql_mode

When setting the SQL mode, you need to specify a value consisting of a single schema value or multiple schema values separated by commas, or an empty string to clear the value. Mode values are not case sensitive.

If you want to set the SQL mode when you start the server, you can set the system variable sql_mode on the mysqld command line or in an options file. You can use the following statement:

Sql_mode= "TRADITIONAL" sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"

If you just want to change the SQL mode at run time, you can use the SET statement to set the sql_mode system variable.

SET sql_mode = 'TRADITIONAL'

If you want to set global SQL mode, you need to add the GLOBAL keyword:

SET GLOBAL sql_mode = 'TRADITIONAL'

Setting global variables requires SUPER administrative privileges. The newly set global variable value will become the default SQL mode for connecting to the client later.

If you want to get the current session or global SQL schema value, you can use the following statement:

SELECT @ @ SESSION.sql_mode;SELECT @ @ GLOBAL. Sql_mode

Its return value consists of all modes currently enabled, separated by commas. If no mode is currently enabled, a null value is returned.

The above is what the SQL mode of MySQL server is. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.