MySQL decimal unsigned update negative numbers converted to 0

Today, when verifying the concurrency problem of the interface, the concurrency pressure previously solved by redis was transferred to mysql (redis has a delay when setting and saving data and needs to obtain data from the database when the data expires, and there will be a gap that causes the risk of large concurrency and multiple data insertion; due to load balancing, PHP's semaphore cannot be used, so we have to use mysql's update to solve the concurrency. After setting the index, the speed is not slow, but it will be under pressure). I found that when updating a field (with the attribute of decimal unsigned), filling in a negative value will not cause an error and will be automatically converted to 0. After searching for a long time, I found that it was STRICT_TRANS_TABLES in my.cnf that was causing the problem.

At this time, you need to understand sql_mode first

The MySQL server can operate in different SQL modes, and can apply different modes to different clients. This way each application can customize the server's operating mode according to its own needs. A mode defines which SQL syntax MySQL should support, and what kind of data validation checks should be performed. This makes it easier to use MySQL in different environments and to use MySQL in conjunction with other database servers. You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option. This value can also be empty (--sql-mode="") if you want to override it. You can also change the SQL mode after startup by setting the sql_mode variable with the SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting a GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that point on. Setting a SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

sql_mode is a list of different modes separated by commas (','). You can query the current mode with the SELECT @@sql_mode statement. The default value is empty (no mode is set).

The main important sql_mode values ​​are:

ANSI

Changes syntax and behavior to be more consistent with standard SQL.

STRICT_TRANS_TABLES

If the given value cannot be inserted into the transaction table, the statement is aborted. For nontransactional tables, if the value appears in a single-row statement or in row 1 of a multi-row statement, the statement is abandoned.

TRADITIONAL

Make MySQL behave like a "traditional" SQL database system. A simple description of this mode is "give an error instead of a warning" when an incorrect value is inserted into a column. Note: Abort the INSERT/UPDATE immediately if an error is found. This is not what you want if you are using a non-transactional storage engine, because data changes made before the error will not be "rolled over", resulting in "partially executed" updates.

This manual refers to "strict mode" to mean a mode in which at least STRICT _TRANS_TABLES or STRICT _ALL_TABLES is enabled.

The following are the differences between STRICT_TRANS_TABLES and STRICT_ALL_TABLES modes:

For transactional tables, when STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled, an error occurs if there are illegal or missing values ​​in the statement. The statement is abandoned and rolled.

For nontransactional tables, both modes behave identically if a bad value occurs in the first row of an insert or update. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows, and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

For STRICT_ALL_TABLES , MySQL returns an error and ignores the remaining rows. However, in this case, the preceding row has already been inserted or updated. This means you can do a partial update, which is probably not what you want. To avoid this, it is best to use single-row statements, since they can be discarded without changing the table. For STRICT_TRANS_TABLES , MySQL converts the illegal value to the closest legal value for the column and inserts the adjusted value. If the value is missing, MySQL inserts an implicit default value in the column. In either case, MySQL generates a warning instead of giving an error and continuing to execute the statement.

Other modes include:

ALLOW_INVALID_DATES: Does not fully check the validity of the date, only checks whether the month is between 1 and 12, and whether the day is between 1 and 31; only valid for DATE and DATETIME, but not for TIMESTAMP, because TIMESTAMP always requires a valid input.

ANSI_QUOTES: When enabled, you cannot use double quotes to quote strings, because " (double quote) will be interpreted as an identifier

ERROR_FOR_DIVISION_BY_ZERO: When enabled, an error occurs during insert or update if the data is divided by zero (or MOD(x,0)). If not enabled, a warning occurs and the system returns NULL when the data is divided by zero.

HIGH_NOT_PRECEDENCE: When enabled, you can get the priority of the previous old version:

NO_AUTO_CREATE_USER: Prevents GRANT from creating users with empty passwords.

NO_AUTO_VALUE_ON_ZERO: Inserting 0 or NULL into an auto-increment column will not be the next auto-increment value.

NO_BACKSLASH_ESCAPES: Backslash "\" is treated as a normal character instead of an escape character

NO_DIR_IN_CREATE: Ignore all index directory and data directory options when creating a table.

NO_ENGINE_SUBSTITUTION: When enabled, if the required storage engine is disabled or not compiled, an error is thrown; if not enabled, the default storage engine will be used instead and an exception will be thrown.

NO_UNSIGNED_SUBSTRACTION: When enabled, subtracting two UNSIGNED types returns a SIGNED type.

NO_ZERO_DATE: When enabled, zero dates such as "0000-00-00 00:00:00" are not allowed to be inserted. This will throw an error. If not enabled, they can be inserted but only a warning will be thrown.

NO_ZERO_IN_DATE: When enabled, the month and day are not allowed to be zero. When enabled together with NO_ZERO_DATE, such as "1999-01-00", an error will be thrown instead of a warning. If this option is enabled alone, a warning will be thrown and then "0000-00-00 00:00:00" will be inserted.

ONLY_FULL_GROUP_BY: For GROUP BY aggregation operations, if the columns in the select do not appear in the group by, then this SQL statement is illegal.

PAD_CHAR_TO_FULL_LENGTH: When enabled, the CHAR type will not truncate empty data;

PIPES_AS_CONCAT: Treat "||" as a concatenation operator instead of an "or" operator.

REAL_AS_FLOAT: Treat REAL as a synonym for FLOAT rather than a synonym for DOUBLE.

Combination options:

·ANSI:

REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI

·ORACLE：

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER, ORACLE

TRADITIONAL:

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, TRADITIONAL

MSSQL:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MSSQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, MSSQL

DB2:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, DB2

MYSQL323:

HIGH_NOT_PRECEDENCE, MYSQL323

MYSQL40:

HIGH_NOT_PRECEDENCE, MYSQL40

MAXDB:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER, MAXDB

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.