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

MySQL how to deal with invalid data values, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

How does MySQL handle invalid data values

By default, MySQL handles out-of-bounds (that is, out-of-range values) and other abnormal values according to the following rules:

For numeric or TIME columns, those values that are outside the legal range are truncated to the end point of the range and the resulting values are stored.

For columns of any type other than the TIME column, the illegal value is converted to a "zero" value consistent with that type.

For string columns (excluding ENUM or SET), strings that are too long are truncated to the maximum length of the column.

When assigning an ENUM or SET type column, you need to do so according to the list of legal values given in the column definition. If you assign a value that is not a member of an enumeration to an ENUM column, the value of the column becomes an empty string. If you assign a value to a SET column that contains substrings that are not members of the collection, the strings are cleaned up and the remaining members are assigned to the column.

If the above conversion occurs during the execution of statements such as additions, deletions, modifications, and queries, MySQL will give a warning message. After executing one of these statements, you can use the SHOW WARNINGS statement to view the contents of the warning message.

If you need to perform stricter checks when inserting or updating data, you can enable one of the following two SQL modes:

SET sql_mode = 'STRICT_ALL_TABLES'

SET sql_mode = 'STRICT_TRANS_TABLES'

For tables that support transactions, both modes are the same. If a value is found to be invalid or missing, an error is generated, and the statement aborts execution and rolls back as if nothing had happened.

For tables that do not support transactions, these two modes have the following effects.

1) for both modes, if a value is found to be invalid or missing when inserting or modifying the first row, the result is an error and the statement aborts execution as if nothing had happened. This is very similar to the behavior of transaction tables.

2) in a statement used to insert or modify multiple rows, if an error occurs in a row after the first row, some rows will be modified. These two modes determine whether the statement should be stopped or continued at this point.

In STRICT_ALL_TABLES mode, an error is thrown and the statement stops execution. Because many of the rows affected by this statement have been modified, this will cause a "partial update" problem.

In STRICT_TRANS_TABLES mode, MySQL aborts the execution of statements for non-transactional tables. Only by doing so can we achieve the effect of the transaction table. This effect can only be achieved when an error occurs in the first line. If the error is on a later line, some lines will be modified. Because those changes are irrevocable for non-transactional tables, MySQL continues to execute the statement to avoid the problem of "partial updates." It converts all invalid values to the nearest legal values. For missing values, MySQL sets the column to the implicit default value of its data type

The entered data can be checked more strictly through the following modes:

ERROR_ FOR_ DIVISION_ BY_ ZERO: in strict mode, it prevents values from entering the database if you encounter a divisor of zero. If you are not in strict mode, a warning message is generated and NULL is inserted.

NO_ ZERO_ DATE: in strict mode, it prevents "zero" date values from entering the database.

NO_ ZERO_ IN_ DATE: in strict mode, it prevents incomplete date values with zero parts of the month or day from entering the database.

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. For example, for an integer field, we write data of a string type, and MySQL will not report an error in non-strict mode. If a field of type char or varchar is defined, no error will be reported when the written or updated data exceeds the defined length.

Although we do data validation in the code, it is generally believed that non-strict mode is not good for programming. MySQL to open strict mode from a certain program is also a test of our code, if we do not open strict mode and do not encounter errors in the development process, then there may be incompatibility when launching or code migration, so it is best to open MySQL strict mode in the development process.

You can see whether it is in strict mode or non-strict mode through the select @ @ sql_mode; command.

For example, if you want all storage engines to enable strict mode and check for divide-by-zero errors, you can set the SQL mode as follows:

SET sql_mode 'STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO'

If you want to enable strict mode, along with all the additional restrictions, the easiest way is to enable TRADITIONAL mode:

SET sql_ mode 'TRADITIONAL'

The meaning of TRADITIONAL mode is "enable strict mode, when inserting data into the MySQL database, perform strict validation of the data to ensure that the error data cannot be inserted. When used in transaction tables, transaction rollback occurs."

The strict model can be selectively weakened in some ways. If SQL's ALLOW_ INVALID_ DATES mode is enabled, MySQL will not do a full check on the date section. Instead, it only requires month values between 1-12 and days between 1-31, that is, invalid values such as' 2000-02-30'or '2000-06-31' are allowed.

Another way to stop errors is to use the IGNORE keyword in INSERT or UPDATE statements. In this way, statements that cause errors due to invalid values will only lead to warnings. These options give you the flexibility to choose the correct validity check level for your application.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.