We reveal the magic of MySQL or the severity and softness of MySQL
Very often on the Internet I meet with articles that give a bunch of examples of the supposedly strange behavior of MySQL compared to other databases. To make it clear what I'm talking about, here are some examples:
1. Dividing by zero returns NULL instead of error.
2. Going beyond the range of acceptable values implicitly leads the number to a valid value, and not to an error and rollback of the transaction
3. Inserting the wrong data type also results in an implicit conversion and successful execution of the operation.
I can cite a huge number of such examples, but the purpose of the article is not to make another collection of examples sucked from the finger, but to explain why this or that action occurs. This whole mystique of MySQL has long been described in the documentation and is easy to explain, as you can see for yourself by reading the article to the end.
For me, this is the first article on Habré, so I tried to write meticulously in detail. I am sure that it will be useful to everyone who works with MySQL. Great help in writing the article had a preparation for the delivery of the certificate developer MySQL, or rather the book "MySQL Certification Study Guide".
So, my friend, let's start!
SQL modes are a configuration of the behavior of the MySQL server, consisting of modes, each of which controls any one aspect of the request processing.
SQL mode features:
1. Sets strict or soft input validation.
2. Enables or disables adherence to SQL standard.
3. Provides better syntax compatibility with other databases.
In fact, SQL mode is a very powerful database tuning mechanism that allows you to flexibly manipulate query processing and MySQL notifications.
')
Before we move on to the theory that follows, you need to strictly understand that changing the SQL mode after creating and inserting data into partitioning tables can lead to significant changes in the behavior of such tables, which in turn can lead to data loss or damage. It is strongly recommended that you never change the SQL mode after creating partition tables.
When replicating partition tables, differing SQL mode parameters on Primary and Slave MySQL servers can also lead to problems. For stable replication between servers, the SQL mode settings must be identical.
Now, after you have realized all the responsibility in using the SQL modes, let's get to its essence.
Control of the current SQL mode occurs through the system variable sql_mode. To set the value, use the SET command. Below are possible options for installing this mode.
1. Corresponds to the default value for the newly installed database (no special modes have been set). Quotes are required.
2. Installing one sql_mode mode. Perhaps two options - with quotes and without them.
3. Installation of several modes sql_mode. Specifying quotes is required!
Despite the fact that the mode names are case-insensitive, I will write them everywhere in the article in upper case for ease of reading.
In the examples above, we set the modes for the current session, but if you have superuser privileges, you can set the global mode for the entire server and all current connections by specifying the GLOBAL parameter. The full syntax for installing sql_mode is as follows:
To view the current values of the global and session mode of the server, use the following queries:
In addition to the SET command, there are two more ways to set the database operation mode:
1. Start the server with the option --sql-mode = "<modes>"
2. Set the parameter sql-mode = "<modes>" in the file my.cnf (for unix-like systems) or my.ini (for windows)
Makes the server interpret a double quote (") in the same way as a reverse quote (`), but it loses its ability to frame lines. As you might guess, this mode makes MySQL move closer to the SQL standard.
By default, no spaces can be set between the function and the opening parenthesis. Enabling this mode allows the server to ignore spaces, but the price for such liberty is that all functions become reserved words, which means that if the name of the column matches the name of the function, it is necessary to screen such column without fail.
When dividing by zero, an error is generated in strict mode, and by a non-strict one - a warning when executing an INSERT or UPDATE statement. Without this parameter, the division by zero returns a warning and inserts a NULL into the table. About severity will be said in the following mode, while trying to abstract.
In the examples above, we received only warnings, because strict mode was turned off. Understanding severity is a very important concept for MySQL databases, because there is no such thing in classic databases. Looking ahead, I will say that all databases are initially strict and do not allow those freedoms that are in MySQL. The softness of MySQL has developed historically when InnoDB was not yet. Judge for yourself, in non-transactional tables there are completely different rules, rather than in transactional ones, so following strict rules would often lead to an undesirable result.
Enables "strict mode" for all tables that support transactions, i.e. on InnoDB and BDB. This mode returns an error instead of a warning in the following cases:
1. Type of input data does not match the specified type. For example, inserting a string into a column with a numeric type
2. The number or date is out of range. The range is determined by the data type. For example, for unsigned tinyint type, the range is from 0 to 255
3. When inserting data, the value of the column is missing, for which the default value is not specified and has the attribute NOT NULL
4. The length of the value is outside the specified range. For example, for a column of type CHAR (5) you cannot insert a line longer than 5 characters.
5. For ENUM and SET types, there is no insert or update value.
More details about the features of this mode will be discussed separately in the subsequent chapter below.
STRICT_ALL_TABLES is completely identical to STRICT_TRANS_TABLES, but the mode's effect already applies to all MySQL tables, and not just transactional ones.
Due to the difference in approaches to the operation of transactional and non-transactional tables, it is not always reasonable to use this mode. If this is not obvious to you, then in the chapters on strict and non-strict regimes you will understand the difference.
Composite mode, includes a whole set of modes, which includes "strict mode", as well as a number of other modes that impose restrictions on the input data.
Causes MySQL to behave like most "traditional" SQL databases.
Let's look at the full list of modes that this mode contains.
Another composite mode that makes MySQL “ANSI-like”, i.e. approximate to the SQL standard.
It includes the following modes: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
The last two modes were discussed earlier, so I will briefly describe the first two:
REAL_AS_FLOAT - the real data type is synonymous with float, not double.
PIPES_AS_CONCAT - allows to use for string concatenation (||), instead of logical OR.
Generates an error in queries in which GROUP BY has an incomplete list of non-aggregated parameters from SELECT and HAVING.
If you want to learn about all the SQL modes and dive deeper into the problem, then you are welcome to the official documentation http://dev.mdq/.com/doc/refman/5.5/en/server-sql-mode.html
To tell the truth, this chapter can hardly be called applied, because in real projects it is necessary to configure directly on the MySQL server, and not by means of a programming language, therefore the chapter is rather theoretical, but for general development it is good to have this method.
The most common connection to the database is through an instance of the PDO class, so we will look at it in detail.
There are two ways to pass special instructions to the database. The first way is to pass in the constructor. Let's look at the full description of the constructor:
I think everything is clear with the first three parameters, but $ driver_options provides us with the ability to perform queries directly in the database. To set the sql_mode system variable as TRADITIONAL, we will do this:
The second way is configuration on the fly, via the setAttribute method;
Of course, some of you may argue that you can execute queries using the query or exec method, but since the chapter is initially theoretical, I will not focus on this method.
More about PDO can be found in the official documentation php.net/manual/ru/book.pdo.php
Predefined PDO constants for working with MySQL php.net/manual/ru/ref.pdo-mysql.php
We already learned a bit about strict mode in the SQL Mode chapter when we studied STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and composite TRADITIONAL modes. Already from the very name it is easy to guess that all input data is checked with special care and in case of violation of any restrictions, an error will inevitably wait for you.
Error in transactional tables causes rollback to be rolled back. Even if your requests are not preceded by the start transaction command, then implicitly each request separately will be wrapped in any way by the start transaction and commit commands. All traditional databases work this way, which equally applies to MySQL transactional tables. From this it follows that violating the constraint, rollback is called, which rolls back all changes.
For non-transactional tables, things are a little more complicated. So, when inserting, updating or deleting several lines, in case of an error, only the last action is canceled, instead of a full rollback. I will illustrate this with an example.
Error generation occurs in the following cases:
1. The type of the inserted data differs from the specified column type.
2. The value is omitted for a column that does not have a default value and has the attribute NOT NULL
3. For numbers and dates - data is outside the range of valid values.
4. For lines - exceeding the permissible length
5. For ENUM and SET types - the value is not valid for the specified enumeration.
6. For a column defined as NOT NULL - insert NULL
If the insert query does not specify data for one of the columns, MySQL will handle this situation in the following order:
1. If the column has a default value, then this value is used and everything ends there; otherwise, the transition to the next step occurs.
2. If the column does not have the parameter NOT NULL, then NULL is assigned and everything ends there, otherwise the behavior depends on the variable sql_mode, more precisely, on the severity of the mode itself.
As you might have guessed from the previous chapter, strict mode will immediately return an error, which in turn will roll back the transaction for transactional tables or cancel the last action for non-transactional tables.
For non-strict mode, the default value for the specified data type will be inserted.
The default values for each data type are:
Hooray! Finally, we got to the most "mysterious" part of the article, which some cover as some kind of MySQL magic, but, alas, these are just tricks on the fun of children. And so, let's go!
Perhaps it was necessary to describe earlier all the cases for which data validation rules apply, but I decided to do it just now. There are only three of them, but each of them requires separate consideration.
Requests for data modification: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Updating table schema description: ALTER TABLE
Setting defaults (DEFAULT) in the column description
I remind you that in strict mode incorrect data will lead to the generation of an error and rollback of the data, and in a non-strict mode, the value will not be explicitly reduced to the correct value and a warning will be generated. To view errors, use SHOW WARNINGS.
Below will be considered in detail all cases of processing incorrect values and their permissions at the database level.
If the number is less than the minimum value of the allowable range, then the minimum acceptable number is assigned. If more than the maximum - the maximum allowable.
Lines longer than a specified length are truncated.
If the value assigned to an ENUM column is not listed in the ENUM definition, then MySQL converts it to an empty string.
If the value that is assigned to the SET column contains elements that are not listed in the SET definition, then MySQL discards these elements, keeping the values only legal elements.
When trying to save a value that cannot be converted to a column data type, MySQL implicitly converts it to the default value for the given type.
The result depends on whether a single row or a set is inserted in an INSERT query.
When inserting a single line, an error occurs and the changes are not applied. With multiple inserts, MySQL implicitly converts the default value for this data type.
When changing the data type, a new type of restrictions is imposed on the column, which can lead to an unexpected change in the data itself according to the rules described above.
If the column is subject to a NOT NULL constraint, then all NULL values are converted to default values for the specified data type of the current column. Default values are described in the “Default Values for Data Types” chapter.
In general, everything has already been said in the last chapter, so there is nothing to add.
Well, my dear reader. Now you can rightly call yourself a real Jedi and get a black belt)))
The IGNORE keyword causes MySQL to include a non-strict mode for such a query. It can also be used to generate a warning instead of an error, in violation of the integrity of the primary key (PRIMARY KEY) or uniqueness (UNIQUE).
This kind of request is not quite an ideal example, but included to once again remember that such a thing exists at all. Used to perform data insertion, or update in case of violation of the integrity of the primary key (PRIMARY KEY) or uniqueness (UNIQUE).
1. Dividing by zero returns NULL instead of error.
2. Going beyond the range of acceptable values implicitly leads the number to a valid value, and not to an error and rollback of the transaction
3. Inserting the wrong data type also results in an implicit conversion and successful execution of the operation.
I can cite a huge number of such examples, but the purpose of the article is not to make another collection of examples sucked from the finger, but to explain why this or that action occurs. This whole mystique of MySQL has long been described in the documentation and is easy to explain, as you can see for yourself by reading the article to the end.
For me, this is the first article on Habré, so I tried to write meticulously in detail. I am sure that it will be useful to everyone who works with MySQL. Great help in writing the article had a preparation for the delivery of the certificate developer MySQL, or rather the book "MySQL Certification Study Guide".
So, my friend, let's start!
SQL Modes
SQL modes are a configuration of the behavior of the MySQL server, consisting of modes, each of which controls any one aspect of the request processing.
SQL mode features:
1. Sets strict or soft input validation.
2. Enables or disables adherence to SQL standard.
3. Provides better syntax compatibility with other databases.
In fact, SQL mode is a very powerful database tuning mechanism that allows you to flexibly manipulate query processing and MySQL notifications.
')
Before we move on to the theory that follows, you need to strictly understand that changing the SQL mode after creating and inserting data into partitioning tables can lead to significant changes in the behavior of such tables, which in turn can lead to data loss or damage. It is strongly recommended that you never change the SQL mode after creating partition tables.
When replicating partition tables, differing SQL mode parameters on Primary and Slave MySQL servers can also lead to problems. For stable replication between servers, the SQL mode settings must be identical.
Now, after you have realized all the responsibility in using the SQL modes, let's get to its essence.
Control of the current SQL mode occurs through the system variable sql_mode. To set the value, use the SET command. Below are possible options for installing this mode.
1. Corresponds to the default value for the newly installed database (no special modes have been set). Quotes are required.
SET sql_mode = ''; 2. Installing one sql_mode mode. Perhaps two options - with quotes and without them.
SET sql_mode = ANSI_QUOTES; SET sql_mode = 'TRADITIONAL'; 3. Installation of several modes sql_mode. Specifying quotes is required!
SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES'; SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIZION_BY_ZERO' Despite the fact that the mode names are case-insensitive, I will write them everywhere in the article in upper case for ease of reading.
In the examples above, we set the modes for the current session, but if you have superuser privileges, you can set the global mode for the entire server and all current connections by specifying the GLOBAL parameter. The full syntax for installing sql_mode is as follows:
SET [GLOBAL|SESSION] sql_mode=''; To view the current values of the global and session mode of the server, use the following queries:
SELECT @@global.sql_mode; SELECT @@session.sql_mode; In addition to the SET command, there are two more ways to set the database operation mode:
1. Start the server with the option --sql-mode = "<modes>"
2. Set the parameter sql-mode = "<modes>" in the file my.cnf (for unix-like systems) or my.ini (for windows)
Quick Reference Mode
ANSI_QUOTES
Makes the server interpret a double quote (") in the same way as a reverse quote (`), but it loses its ability to frame lines. As you might guess, this mode makes MySQL move closer to the SQL standard.
mysql> CREATE TABLE test11 (`order` INT NULL) ENGINE = InnoDB; Query OK, 0 rows affected (0.28 sec) mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" INT NOT NULL) ENGINE = InnoDB' at line 1 mysql> SET sql_mode = 'ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB; Query OK, 0 rows affected (0.08 sec) IGNORE_SPACE
By default, no spaces can be set between the function and the opening parenthesis. Enabling this mode allows the server to ignore spaces, but the price for such liberty is that all functions become reserved words, which means that if the name of the column matches the name of the function, it is necessary to screen such column without fail.
mysql> SELECT COUNT (*) FROM test12; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM test12' at line 1 mysql> SET sql_mode = 'IGNORE_SPACE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT (*) FROM test12; +-----------+ | COUNT (*) | +-----------+ | 0 | +-----------+ 1 row in set (0.01 sec) ERROR_FOR_DIVISION_BY_ZERO
When dividing by zero, an error is generated in strict mode, and by a non-strict one - a warning when executing an INSERT or UPDATE statement. Without this parameter, the division by zero returns a warning and inserts a NULL into the table. About severity will be said in the following mode, while trying to abstract.
mysql> SELECT 1 / 0; +-------+ | 1 / 0 | +-------+ | NULL | +-------+ 1 row in set (0.00 sec) mysql> SHOW WARNINGS; Empty set (0.00 sec) mysql> INSERT INTO test12 VALUES (1/0); Query OK, 1 row affected (0.02 sec) mysql> SHOW WARNINGS; Empty set (0.00 sec) mysql> SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT 1 / 0; +-------+ | 1 / 0 | +-------+ | NULL | +-------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+---------------+ | Level | Code | Message | +---------+------+---------------+ | Warning | 1365 | Division by 0 | +---------+------+---------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test12 VALUES (1/0); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> SHOW WARNINGS; +---------+------+---------------+ | Level | Code | Message | +---------+------+---------------+ | Warning | 1365 | Division by 0 | +---------+------+---------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test12 VALUES ('some string'/0); Query OK, 1 row affected, 2 warnings (0.02 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'some string' | | Warning | 1365 | Division by 0 | +---------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) In the examples above, we received only warnings, because strict mode was turned off. Understanding severity is a very important concept for MySQL databases, because there is no such thing in classic databases. Looking ahead, I will say that all databases are initially strict and do not allow those freedoms that are in MySQL. The softness of MySQL has developed historically when InnoDB was not yet. Judge for yourself, in non-transactional tables there are completely different rules, rather than in transactional ones, so following strict rules would often lead to an undesirable result.
STRICT_TRANS_TABLES
Enables "strict mode" for all tables that support transactions, i.e. on InnoDB and BDB. This mode returns an error instead of a warning in the following cases:
1. Type of input data does not match the specified type. For example, inserting a string into a column with a numeric type
2. The number or date is out of range. The range is determined by the data type. For example, for unsigned tinyint type, the range is from 0 to 255
3. When inserting data, the value of the column is missing, for which the default value is not specified and has the attribute NOT NULL
4. The length of the value is outside the specified range. For example, for a column of type CHAR (5) you cannot insert a line longer than 5 characters.
5. For ENUM and SET types, there is no insert or update value.
More details about the features of this mode will be discussed separately in the subsequent chapter below.
STRICT_ALL_TABLES
STRICT_ALL_TABLES is completely identical to STRICT_TRANS_TABLES, but the mode's effect already applies to all MySQL tables, and not just transactional ones.
Due to the difference in approaches to the operation of transactional and non-transactional tables, it is not always reasonable to use this mode. If this is not obvious to you, then in the chapters on strict and non-strict regimes you will understand the difference.
TRADITIONAL
Composite mode, includes a whole set of modes, which includes "strict mode", as well as a number of other modes that impose restrictions on the input data.
Causes MySQL to behave like most "traditional" SQL databases.
Let's look at the full list of modes that this mode contains.
mysql> SET sql_mode = 'TRADITIONAL'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode\G *************************** 1. row *************************** @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec) ANSI
Another composite mode that makes MySQL “ANSI-like”, i.e. approximate to the SQL standard.
It includes the following modes: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
The last two modes were discussed earlier, so I will briefly describe the first two:
REAL_AS_FLOAT - the real data type is synonymous with float, not double.
PIPES_AS_CONCAT - allows to use for string concatenation (||), instead of logical OR.
ONLY_FULL_GROUP_BY
Generates an error in queries in which GROUP BY has an incomplete list of non-aggregated parameters from SELECT and HAVING.
mysql> SELECT name, address, MAX(age) FROM test GROUP BY name; ERROR 1055 (42000): 't.address' isn't in GROUP BY mysql> SELECT name, MAX(age) as max_age FROM test GROUP BY name HAVING max_age < 30; Empty set (0.00 sec) ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause If you want to learn about all the SQL modes and dive deeper into the problem, then you are welcome to the official documentation http://dev.mdq/.com/doc/refman/5.5/en/server-sql-mode.html
Working with SQL mode in PHP
To tell the truth, this chapter can hardly be called applied, because in real projects it is necessary to configure directly on the MySQL server, and not by means of a programming language, therefore the chapter is rather theoretical, but for general development it is good to have this method.
The most common connection to the database is through an instance of the PDO class, so we will look at it in detail.
There are two ways to pass special instructions to the database. The first way is to pass in the constructor. Let's look at the full description of the constructor:
PDO::__construct() ( string $dsn [, string $username [, string $password [, array $driver_options ]]] ) I think everything is clear with the first three parameters, but $ driver_options provides us with the ability to perform queries directly in the database. To set the sql_mode system variable as TRADITIONAL, we will do this:
$db = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode = 'TRADITIONAL'")); The second way is configuration on the fly, via the setAttribute method;
$db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET sql_mode = 'TRADITIONAL'"); Of course, some of you may argue that you can execute queries using the query or exec method, but since the chapter is initially theoretical, I will not focus on this method.
More about PDO can be found in the official documentation php.net/manual/ru/book.pdo.php
Predefined PDO constants for working with MySQL php.net/manual/ru/ref.pdo-mysql.php
Strict regime
We already learned a bit about strict mode in the SQL Mode chapter when we studied STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and composite TRADITIONAL modes. Already from the very name it is easy to guess that all input data is checked with special care and in case of violation of any restrictions, an error will inevitably wait for you.
Error in transactional tables causes rollback to be rolled back. Even if your requests are not preceded by the start transaction command, then implicitly each request separately will be wrapped in any way by the start transaction and commit commands. All traditional databases work this way, which equally applies to MySQL transactional tables. From this it follows that violating the constraint, rollback is called, which rolls back all changes.
For non-transactional tables, things are a little more complicated. So, when inserting, updating or deleting several lines, in case of an error, only the last action is canceled, instead of a full rollback. I will illustrate this with an example.
mysql> SET sql_mode = 'STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test20 ( -> x TINYINT NOT NULL, -> y TINYINT NOT NULL -> ) ENGINE = MyISAM; Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO test20 VALUES (1, 1), (2, 2), (3, 3), (NULL, 4), (5, 5); ERROR 1048 (23000): Column 'x' cannot be null mysql> SELECT * FROM test20; +---+---+ | x | y | +---+---+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +---+---+ 3 rows in set (0.01 sec) mysql> UPDATE test20 SET x = x + 125, y = y + 1; ERROR 1264 (22003): Out of range value for column 'x' at row 3 mysql> SELECT * FROM test20; +-----+---+ | x | y | +-----+---+ | 126 | 2 | | 127 | 3 | | 3 | 3 | +-----+---+ 3 rows in set (0.00 sec) Error generation occurs in the following cases:
1. The type of the inserted data differs from the specified column type.
2. The value is omitted for a column that does not have a default value and has the attribute NOT NULL
3. For numbers and dates - data is outside the range of valid values.
4. For lines - exceeding the permissible length
5. For ENUM and SET types - the value is not valid for the specified enumeration.
6. For a column defined as NOT NULL - insert NULL
Default values for data types
If the insert query does not specify data for one of the columns, MySQL will handle this situation in the following order:
1. If the column has a default value, then this value is used and everything ends there; otherwise, the transition to the next step occurs.
2. If the column does not have the parameter NOT NULL, then NULL is assigned and everything ends there, otherwise the behavior depends on the variable sql_mode, more precisely, on the severity of the mode itself.
As you might have guessed from the previous chapter, strict mode will immediately return an error, which in turn will roll back the transaction for transactional tables or cancel the last action for non-transactional tables.
For non-strict mode, the default value for the specified data type will be inserted.
The default values for each data type are:
- For int and float types, the default value is zero (0)
- For all lines except the enum type, the empty string ('')
- For enum, the first numbered member
- For date and time - zero values '0000-00-00 00:00:00'
- For timestamp - the current date and time, if the column was automatically initialized, or zero values of '0000-00-00 00:00:00'
Lax mode
Hooray! Finally, we got to the most "mysterious" part of the article, which some cover as some kind of MySQL magic, but, alas, these are just tricks on the fun of children. And so, let's go!
Perhaps it was necessary to describe earlier all the cases for which data validation rules apply, but I decided to do it just now. There are only three of them, but each of them requires separate consideration.
Requests for data modification: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Updating table schema description: ALTER TABLE
Setting defaults (DEFAULT) in the column description
I remind you that in strict mode incorrect data will lead to the generation of an error and rollback of the data, and in a non-strict mode, the value will not be explicitly reduced to the correct value and a warning will be generated. To view errors, use SHOW WARNINGS.
Below will be considered in detail all cases of processing incorrect values and their permissions at the database level.
Out of range
If the number is less than the minimum value of the allowable range, then the minimum acceptable number is assigned. If more than the maximum - the maximum allowable.
mysql> CREATE TABLE test31 ( -> i TINYINT NOT NULL, -> j TINYINT NOT NULL, -> k TINYINT NOT NULL -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO test31 (i, j, k) VALUES (-500, 10, 500); Query OK, 1 row affected, 2 warnings (0.06 sec) mysql> SELECT * FROM test30; +------+----+-----+ | i | j | k | +------+----+-----+ | -128 | 10 | 127 | +------+----+-----+ 1 row in set (0.00 sec) Processing strings
Lines longer than a specified length are truncated.
mysql> CREATE TABLE test32 ( col1 VARCHAR(10) ) ENGINE = InnoDB; Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO test32 VALUES ('I\m really the biggest string'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'col1' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test32; +------------+ | col1 | +------------+ | Im really | +------------+ 1 row in set (0.00 sec) ENUM and SET data types
If the value assigned to an ENUM column is not listed in the ENUM definition, then MySQL converts it to an empty string.
If the value that is assigned to the SET column contains elements that are not listed in the SET definition, then MySQL discards these elements, keeping the values only legal elements.
mysql> CREATE TABLE test33 ( -> col1 ENUM('One', 'Two', 'Three'), -> col2 SET('One', 'Two', 'Three') -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO test33 VALUES ('Five', 'One,Three,Five'); Query OK, 1 row affected, 2 warnings (0.04 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'col1' at row 1 | | Warning | 1265 | Data truncated for column 'col2' at row 1 | +---------+------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM test33; +------+-----------+ | col1 | col2 | +------+-----------+ | | One,Three | +------+-----------+ 2 rows in set (0.00 sec) Conversion to date type
When trying to save a value that cannot be converted to a column data type, MySQL implicitly converts it to the default value for the given type.
mysql> CREATE TABLE test34 ( -> col1 DATETIME, -> col2 DATETIME -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.14 sec) mysql> INSERT INTO test34 VALUES ('string', 123); Query OK, 1 row affected, 1 warning (0.12 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1264 | Out of range value for column 'col1' at row 1 | +---------+------+-----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test34; +---------------------+---------------------+ | col1 | col2 | +---------------------+---------------------+ | 0000-00-00 00:00:00 | 2000-01-23 00:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) Conversion table
| STRING | DATE | Int |
| '2010-03-12' | '2010-03-12' | 2010 |
| '03 -12-2010 ' | '0000-00-00' | 3 |
| '0017' | '0000-00-00' | 17 |
| '500 hats' | '0000-00-00' | 500 |
| 'bartholomew' | '0000-00-00' | 0 |
Assigning NULL to a column with NOT NULL
The result depends on whether a single row or a set is inserted in an INSERT query.
When inserting a single line, an error occurs and the changes are not applied. With multiple inserts, MySQL implicitly converts the default value for this data type.
Updating table schema description: ALTER TABLE
When changing the data type, a new type of restrictions is imposed on the column, which can lead to an unexpected change in the data itself according to the rules described above.
If the column is subject to a NOT NULL constraint, then all NULL values are converted to default values for the specified data type of the current column. Default values are described in the “Default Values for Data Types” chapter.
mysql> CREATE TABLE test35 ( -> i INT NULL, -> i2 INT NOT NULL, -> j VARCHAR(20) NOT NULL, -> k DATETIME -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.15 sec) mysql> INSERT INTO test35 VALUES (NULL, 9999, 'very cool string', '1910-01-01 12:10:00'); Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM test35; +------+------+------------------+---------------------+ | i | i2 | j | k | +------+------+------------------+---------------------+ | NULL | 9999 | very cool string | 1910-01-01 12:10:00 | +------+------+------------------+---------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE test35 -> CHANGE COLUMN `i` `i` INT(11) NOT NULL, -> CHANGE COLUMN `i2` `i2` TINYINT(1) NOT NULL, -> CHANGE COLUMN `j` `j` VARCHAR(2) NOT NULL, -> CHANGE `k` `k` TIMESTAMP NULL DEFAULT NULL; Query OK, 1 row affected, 4 warnings (0.42 sec) Records: 1 Duplicates: 0 Warnings: 4 mysql> SHOW WARNINGS; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1265 | Data truncated for column 'i' at row 1 | | Warning | 1264 | Out of range value for column 'i2' at row 1 | | Warning | 1265 | Data truncated for column 'j' at row 1 | | Warning | 1264 | Out of range value for column 'k' at row 1 | +---------+------+---------------------------------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM test35; +---+-----+----+---------------------+ | i | i2 | j | k | +---+-----+----+---------------------+ | 0 | 127 | ve | 0000-00-00 00:00:00 | +---+-----+----+---------------------+ 1 row in set (0.00 sec) Setting defaults (DEFAULT) in the column description
In general, everything has already been said in the last chapter, so there is nothing to add.
Well, my dear reader. Now you can rightly call yourself a real Jedi and get a black belt)))
Removing the pros
IGNORE
The IGNORE keyword causes MySQL to include a non-strict mode for such a query. It can also be used to generate a warning instead of an error, in violation of the integrity of the primary key (PRIMARY KEY) or uniqueness (UNIQUE).
mysql> CREATE TABLE test40 ( -> x INT NOT NULL PRIMARY KEY -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.14 sec) mysql> INSERT IGNORE INTO test40 VALUES (1), (2), (2), (3), (4); Query OK, 4 rows affected (0.02 sec) Records: 5 Duplicates: 1 Warnings: 0 mysql> SELECT * FROM test40; +---+ | x | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.02 sec) mysql> INSERT INTO test40 VALUES (1), (2), (2), (3), (4); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> UPDATE IGNORE test40 SET x = 3 WHERE x = 2; Query OK, 0 rows affected (0.07 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE test40 SET x = 3 WHERE x = 2; ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' ON DUPLICATE KEY UPDATE
This kind of request is not quite an ideal example, but included to once again remember that such a thing exists at all. Used to perform data insertion, or update in case of violation of the integrity of the primary key (PRIMARY KEY) or uniqueness (UNIQUE).
mysql> INSERT INTO test40 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10; Query OK, 1 row affected (0.25 sec) mysql> SELECT * FROM test40; +----+ | x | +----+ | 1 | | 2 | | 3 | | 4 | | 10 | +----+ 5 rows in set (0.06 sec) mysql> INSERT INTO test40 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10; Query OK, 2 rows affected (0.07 sec) mysql> SELECT * FROM test40; +----+ | x | +----+ | 1 | | 2 | | 3 | | 4 | | 20 | +----+ 5 rows in set (0.00 sec) Source: https://habr.com/ru/post/166411/
All Articles