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

I believe many inexperienced people don't know what to do about how to use STRICT_TRANS_TABLES in MYSQL. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

MYSQL STRICT_TRANS_TABLES illustration tutorial

When mysql is in insert, it is often prompted that there is no default value, and MySQL 1364 error prompt: # 1364-Field "XXX" doesn't have a default value. The XXX field does not have a default numeric value; that is, we do not assign a numeric value to it, and the default value is not set for this field in the table. This is the mysql mode to enable the storage engine to enable strict mode, illegal data values are rejected, I posted the following picture of the actual operation problems:

And the fields in my Tbl_User table:

Www.2cto.com

Solution: in the configuration file my.ini of mysql

XML/HTML code

01.# Set the SQL mode to strict # sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

02. Change to:

03.

04.sqlMurmode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To get rid of the strict mode, you can turn on mysql.

Post a detailed description of the relevant models on the Internet:

NO_AUTO_CREATE_USER

Www.2cto.com

Prevents GRANT from automatically creating new users unless a password is also specified.

NO_UNSIGNED_SUBTRACTION

In subtractive operands, do not mark the result as UNSIGNED if an Operand is unsigned. Note that this prevents UNSIGNED BIGINT from being used in context.

Post a few other modes:

In mysql 5, the default is REAL_AS_FLOAT,PIPES_AS_CONTACT,ANSI_QUOTES,GNORE_SPACE and ANSI

In this mode, values that exceed the length of the field are allowed to be inserted, but after insertion, warnings are returned instead of errors, when STRICT_TRANS_TABLES is used

It is a mistake, a strict warning.

NO_AUTO_CREATE_USER

Prevents GRANT from automatically creating new users unless a password is also specified.

Here are some introductions in the manual

All supported modes are described below:

ALLOW_INVALID_DATES

Do not check all dates in strict mode. Check only the months between 1 and 12 and the days between 1 and 31. This is important in Web applications when you get the year, month, and day from three different fields and want to save exactly what the user inserts (without date verification). This mode applies to DATE and DATETIME columns. Not suitable for TIMESTAMP column, TIMESTAMP column requires validation date.

When strict mode is enabled, the server needs a legal month and day, not just between 1 to 12 and 1 to 31, respectively. For example, '2004-04-31' is legal when strict mode is disabled, but illegal when strict mode is enabled. To allow masking of fixed dates in strict mode, ALLOW_INVALID_DATES should also be enabled.

Www.2cto.com

ANSI_QUOTES

Think of'"'as identifier quotation marks ('quotation mark characters), not as quotation mark characters of a string. In ANSI mode, you can still use' `'to reference identifiers. When ANSI_QUOTES is enabled, you cannot use double quotes to refer to a string because it is interpreted as an identifier.

ERROR_FOR_DIVISION_BY_ZERO

In strict mode, if divided by zero (or MOD (XP0)) during the INSERT or UPDATE process, an error (otherwise a warning) is generated. If the pattern is not given, MySQL returns NULL when divided by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generation is divided by zero warning, but the result is NULL.

HIGH_NOT_PRECEDENCE

The priority of the NOT operator is that expressions such as NOT a BETWEEN b AND c are interpreted as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was interpreted as (NOT a) BETWEEN b AND c. By enabling HIGH_NOT_PRECEDENCESQL mode, you can get previous higher priority results.

Mysql > SET sql_mode =''; mysql > SELECT NOT 1 BETWEEN-5 AND 5;-> 0 mysql > SET sql_mode = 'broken_not'; mysql > SELECT NOT 1 BETWEEN-5 AND 5;-> 1

IGNORE_SPACE

A space is allowed between the function name and'('. Forces all function names to be treated as saved words. As a result, if you want to access a saved word, table, or column name, you must reference it. For example, because there is a USER () function, the name of the user table in the database and the User column in the table are saved, so you must reference them:

SELECT "User" FROM mysql. "user"

NO_AUTO_CREATE_USER

Prevents GRANT from automatically creating new users unless a password is also specified.

Www.2cto.com

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects the processing of AUTO_INCREMENT columns. In general, you can insert NULL or 0 into the column to generate the next serial number. NO_AUTO_VALUE_ON_ZERO disables 0, so only NULL can generate the next serial number.

This schema is useful if you save 0 to the AUTO_INCREMENT column of the table. (this practice is not recommended.) For example, if you dump the table with mysqldump and reload it, MySQL will generally generate a new serial number when it encounters a value of 0, and the contents of the generated table are different from those of the dumped table. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. Mysqldump automatically includes NO_AUTO_VALUE_ON_ZERO-enabled statements in the output.

NO_BACKSLASH_ESCAPES

Disables the backslash character ('') as the exit character within the string. When this mode is enabled, the backslash becomes a normal character.

NO_DIR_IN_CREATE

All INDEX DIRECTORY and DATA DIRECTORY instructions are ignored when creating tables. This option is useful from the replication server.

NO_ENGINE_SUBSTITUTION

If the required storage engine is disabled or uncompiled, you can prevent automatic replacement of the storage engine.

NO_FIELD_OPTIONS

Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used for mysqldump in Portable Mode (portability mode).

NO_KEY_OPTIONS

Do not print the MySQL-specific index option in the output of SHOW CREATE TABLE. This mode is used for mysqldump in Portable Mode (portability mode).

NO_TABLE_OPTIONS

Www.2cto.com

Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used for mysqldump in Portable Mode (portability mode).

NO_UNSIGNED_SUBTRACTION

In subtractive operands, do not mark the result as UNSIGNED if an Operand is unsigned. Note that this prevents UNSIGNED BIGINT from being used in context. NO_ZERO_DATE

In strict mode, do not use '0000-00' as the legal date. You can still insert zero dates with the IGNORE option. In non-strict mode, the date can be accepted, but a warning is generated.

NO_ZERO_IN_DATE

In strict mode, dates with a month or day part of 0 are not accepted. If we use the IGNORE option, we insert '0000-00-00' for similar dates. In non-strict mode, the date can be accepted, but a warning is generated.

ONLY_FULL_GROUP_BY

Do not let the query in the GROUP BY section point to an unselected column.

PIPES_AS_CONCAT

Treat | as a string concatenation operator (+) (same as CONCAT ()), not as OR.

REAL_AS_FLOAT

Consider REAL as a synonym for FLOAT, not a synonym for DOUBLE.

STRICT_TRANS_TABLES

Enable strict mode for all storage engines. Illegal data value is rejected.

STRICT_TRANS_TABLES

Strict mode is enabled for the transactional storage engine and may also be enabled for the non-transactional storage engine. It is explained in detail later.

Strict mode controls how MySQL handles illegal or missing input values. There are several reasons to make a value illegal. For example, the data type is incorrect, does not fit the column, or is out of range. When the newly inserted row does not contain a column that does not display a value that defines the DEFAULT clause, the value is lost. Www.2cto.com

For transaction tables, an error occurs if there are illegal or missing values in the statement when STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled. Statement is discarded and scrolled.

For non-transactional tables, if a bad value appears in the first row of the insert or update, the behavior of the two modes is the same. The statement is discarded and the table remains unchanged. If the statement inserts or modifies multiple rows and bad values appear in the second or later row, the result depends on which strict option is enabled:

Returns an error for STRICT_ALL_TABLES,MySQL and ignores the remaining lines. However, in this case, the previous row has been inserted or updated. This means that you can update partially, which may not be what you want. To avoid this, it's best to use a single-line statement, because you can give up without changing the table.

For STRICT_TRANS_TABLES,MySQL, convert the illegal value to the legal value closest to the column and insert the adjusted value. If the value is missing, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of giving an error and continues to execute the statement. Describes the implicit default value.

Strict mode does not allow illegal dates, such as' 2004-04-31'. It does not allow the use of "zero" parts of dates, such as' 2004-04-00' or 'zero' dates. To prohibit, NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes should be enabled on a strict mode basis.

Www.2cto.com

If you do not use strict mode (that is, do not enable STRICT_TRANS_TABLES or STRICT_ALL_TABLES mode), MySQL will insert the adjusted value and give a warning for illegal or missing values. In strict mode, you can do this through INSERT IGNORE or UPDATE IGNORE.

The following special patterns quickly combine the patterns listed above.

This includes all the schema values in most of the latest versions of MySQL. In the old version, the combination mode did not include inapplicable specific pattern values that were not available in the new version.

Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MAXDB

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

MSSQL

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MYSQL323

Equivalent to NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE.

MYSQL40

Www.2cto.com

Equivalent to NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE.

ORACLE

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

POSTGRESQL

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

TRADITIONAL

Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER

(2) sql code

1.select @ @ sql_mode

two。

3.CREATE TABLE `t` (

4. `name` varchar (20)

5. `email` varchar (40)

6.); www.2cto.com

7.desc t

8.

9.set session sql_mode= "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI"

10.

11.insert into t (`name`, `email`) values ("12345678901234567890999999", "beijing2008@126.com")

twelve。

13.set session sql_mode= "STRICT_TRANS_TABLES"

After reading the above, have you mastered how to use STRICT_TRANS_TABLES in MYSQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.