January 21, 2013 at 12:20We reveal the magic of MySQL or the rigor and softness of MySQL
- From the sandbox
- Tutorial
Very often on the Internet I come across articles that give a bunch of examples with the supposedly strange behavior of MySQL compared to other databases. To make it clear what I'm talking about, I will give a few examples:
1. Division by zero returns NULL instead of an error
2. Exceeding the range of acceptable values implicitly leads the number to a valid value, and not to a transaction error and rollback
3. Inserting an incorrect data type also leads to an implicit transformation and successful operation.
I can cite a huge number of such examples, but the purpose of the article is not to make the next collection of examples sucked from the finger, but to explain why an action occurs. All this mysticism 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 the hub, 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 was provided by the preparation for the MySQL developer certificate, or rather, the MySQL Certification Study Guide.
So my friend, let's get started!
SQL modes is a MySQL server behavior setting that consists of modes, each of which controls one aspect of query processing.
Features of SQL mode:
1. Sets strict or soft check of input data
2. Enables or disables following 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 next theory, you should be very careful to understand that changing 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 loss or damage to data. It is highly recommended that you never change SQL mode after creating partition tables.
When replicating partition tables, differing SQL mode parameters on the 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 SQL modes, let's move on to its essence.
The control of the current SQL mode occurs through the sql_mode system variable. To set the value, use the SET command. Below are the possible installation options for this mode.
1. Corresponds to the default value for the newly installed database (no special modes are set). Quotation marks are required.
2. Setting one sql_mode mode. Two options are possible - with and without quotation marks.
3. Setting multiple sql_mode modes. Quotation marks are required!
Despite the fact that the names of the modes are case-insensitive, for convenience of reading I will write them in upper case everywhere in the article.
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 server mode, use the following queries:
In addition to the SET command, there are two more ways to set the database operating mode:
1. Start the server with the option --sql-mode = "<modes>"
2. Install in the my.cnf file (for unix-like systems) or my.ini (for windows ) parameter sql-mode = "<modes>"
It forces the server to interpret the double quote (") just like the back quote (`), but it loses the ability to frame strings. As you might have guessed, this mode makes MySQL come close to SQL standard.
By default, spaces cannot be inserted between a function and an opening parenthesis. Enabling this mode allows the server to ignore spaces, but the fee for such freedom will be that all functions will become reserved words, which means that if the column name coincides with the function name, you will have to screen such a column.
When dividing by zero in strict mode, an error is generated, and non-strict warning is generated when executing INSERT or UPDATE statements. Without this parameter division by zero returns a warning and inserts NULL into the table. About rigor will be said in the next mode, while trying to abstract.
In the above examples, we received only warnings, because strict mode was turned off. Understanding strictness is a very important concept for a MySQL database, because there is no such thing in classic databases. Looking ahead, I’ll say that all databases are initially strict and do not allow those liberties that are in MySQL. The softness of MySQL has developed historically when InnoDB was not yet. Judge for yourself, in non-transactional tables, completely different rules apply than in transactional ones, therefore, 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. The input data type does not match the specified type. For example, inserting a row into a column with a numeric type
2. A number or date is out of range. The range is determined by the data type. For example, for the unsigned tinyint type, the valid range is numbers from 0 to 255
3. When inserting data, the value of the column is omitted, which does not have a default value and has the NOT NULL attribute
4. The length of the value is outside the specified range. For example, for a column of type CHAR (5), you cannot insert a string of more than 5 characters
5. For types ENUM and SET, there is no inserted or updated value
More details about the features of this mode will be discussed separately in the next chapter.
STRICT_ALL_TABLES is completely identical to STRICT_TRANS_TABLES, but the mode already applies to all MySQL tables, not just transactional ones.
Due to the difference in the approaches to the operation of transactional and non-transactional tables, it does not always make sense to use this mode. If this is not yet obvious to you, then in the chapters on strict and non-strict modes 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.
Makes MySQL 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. close 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 data type real 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 does not have a complete list of non-aggregated parameters from SELECT and HAVING.
If you want to learn about all SQL mode modes and dive deeper into the problem, then you are welcome to the official documentation http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
In truth, this chapter can hardly be called applied, because in real projects you need to configure it directly on the MySQL server, and not by means of the programming language, therefore the chapter is more theoretical, but for general development it’s good to keep in mind this way.
Most often, a connection to the database occurs through an instance of the PDO class, so we will consider it in detail.
There are two ways to pass special instructions to the database. The first way is passing in the constructor. Let's look at the full description of the constructor:
I think that with the first three parameters everything is clear, but $ driver_options just gives us the ability to execute queries directly in the database. To set the sql_mode system variable to TRADITIONAL mode, we will do this:
The second way is on-the-fly configuration through the setAttribute method;
Of course, some of you may object that you can execute queries using the query or exec method, but since the chapter is originally theoretical, I will not focus on this method.
You can read more about PDO 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 the STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and composite TRADITIONAL modes. Already from the name itself it is easy to guess that all input data are checked with special care and in case of violation of any restrictions, an error will inevitably await you.
An error in transactional tables causes a rollback of the transaction (rollback). Even if your requests are not preceded by the start transaction command, then implicitly each request individually will be wrapped in the start transaction and commit commands in any way. This is how all traditional databases work, which applies equally to transactional MySQL tables. From this it follows that, having violated the restriction, rollback is called, which rolls back all the changes.
For non-transactional tables, things are a little more complicated. So, when inserting, updating or deleting several rows, in case of an error only the last action is canceled, instead of a complete rollback. I will illustrate this with an example.
An error is generated in the following cases:
1. The type of inserted data differs from the specified type of column
2. The value for the column that does not have a default value and has the NOT NULL attribute is omitted
3. For numbers and dates, the data is outside the range of acceptable values
4. For rows, the length is exceeded
5. For types ENUM and SET, the value is not valid for the specified enumeration
6. For a column defined as NOT NULL, insert NULL
If the data for one of the columns is not specified in the insert query, then MySQL will handle this situation in the following order:
1. If the column has a default value, then this value is used and that’s it, otherwise it will go to the next step
2 If the column does not have the NOT NULL parameter, then NULL is assigned and this all ends, otherwise the behavior depends on the sql_mode variable, more precisely, on the strictness 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 undo the last action for non-transactional tables.
For soft mode, a default value for the specified data type will be inserted.
The default values for each data type are:
Hurrah! Finally, we got to the very “mysterious” part of the article, which some cover as some kind of MySQL magic, but, alas, these are just tricks for children's fun. And so, let's go!
Perhaps you should have previously described all the cases for which data validation rules apply, but I decided to do this only now. There are only three of them, but each of them requires a separate consideration.
Data modification requests: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Updating the description of table schemas: ALTER TABLE
Setting the default value (DEFAULT) in the column description
I remind you that in strict mode incorrect data will lead to error generation and data rollback, and in non-strict mode, the value will not be explicitly converted to the correct value and a warning will be generated. Use SHOW WARNINGS to view errors.
Below we will consider 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 allowable number is assigned. If more than the maximum - the maximum allowable.
Lines longer than the specified length are truncated.
If the value assigned to an ENUM column is not listed in the ENUM definition, then MySQL will convert 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, storing values only to 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 this type.
The result depends on whether one row or many will be inserted in the INSERT query.
When inserting a single row, an error occurs and the changes are not applied. For multiple insertion - MySQL implicitly converts the default value for this data type.
When changing the data type, the restrictions of the new type are imposed on the column, which can lead to an unexpected change in the data itself according to the rules described above.
If a NOT NULL constraint is imposed on a column, then all NULL values are converted to default values for the specified data type of the current column. The default values are described in the chapter "Default values for data types."
In general, everything has already been said in the last chapter, so there is nothing to add.
Well, my dear reader. Now you can rightfully be called a real Jedi and get a black belt)))
The IGNORE keyword forces MySQL to enable lax mode for such a query. It can also be used to generate a warning instead of an error in case of violation of the integrity of the primary key (PRIMARY KEY) or uniqueness (UNIQUE).
This type of request is not quite an ideal example, but it was included in order to once again recall that such a thing exists at all. It is used to perform data insertion, or updating in violation of the primary key integrity restriction (PRIMARY KEY) or uniqueness (UNIQUE).
1. Division by zero returns NULL instead of an error
2. Exceeding the range of acceptable values implicitly leads the number to a valid value, and not to a transaction error and rollback
3. Inserting an incorrect data type also leads to an implicit transformation and successful operation.
I can cite a huge number of such examples, but the purpose of the article is not to make the next collection of examples sucked from the finger, but to explain why an action occurs. All this mysticism 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 the hub, 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 was provided by the preparation for the MySQL developer certificate, or rather, the MySQL Certification Study Guide.
So my friend, let's get started!
SQL Modes
SQL modes is a MySQL server behavior setting that consists of modes, each of which controls one aspect of query processing.
Features of SQL mode:
1. Sets strict or soft check of input data
2. Enables or disables following 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 next theory, you should be very careful to understand that changing 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 loss or damage to data. It is highly recommended that you never change SQL mode after creating partition tables.
When replicating partition tables, differing SQL mode parameters on the 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 SQL modes, let's move on to its essence.
The control of the current SQL mode occurs through the sql_mode system variable. To set the value, use the SET command. Below are the possible installation options for this mode.
1. Corresponds to the default value for the newly installed database (no special modes are set). Quotation marks are required.
SET sql_mode = '';
2. Setting one sql_mode mode. Two options are possible - with and without quotation marks.
SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';
3. Setting multiple sql_mode modes. Quotation marks are required!
SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIZION_BY_ZERO'
Despite the fact that the names of the modes are case-insensitive, for convenience of reading I will write them in upper case everywhere in the article.
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 server mode, 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 operating mode:
1. Start the server with the option --sql-mode = "<modes>"
2. Install in the my.cnf file (for unix-like systems) or my.ini (for windows ) parameter sql-mode = "<modes>"
Quick Reference Mode
ANSI_QUOTES
It forces the server to interpret the double quote (") just like the back quote (`), but it loses the ability to frame strings. As you might have guessed, this mode makes MySQL come close to 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, spaces cannot be inserted between a function and an opening parenthesis. Enabling this mode allows the server to ignore spaces, but the fee for such freedom will be that all functions will become reserved words, which means that if the column name coincides with the function name, you will have to screen such a column.
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 in strict mode, an error is generated, and non-strict warning is generated when executing INSERT or UPDATE statements. Without this parameter division by zero returns a warning and inserts NULL into the table. About rigor will be said in the next 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 above examples, we received only warnings, because strict mode was turned off. Understanding strictness is a very important concept for a MySQL database, because there is no such thing in classic databases. Looking ahead, I’ll say that all databases are initially strict and do not allow those liberties that are in MySQL. The softness of MySQL has developed historically when InnoDB was not yet. Judge for yourself, in non-transactional tables, completely different rules apply than in transactional ones, therefore, 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. The input data type does not match the specified type. For example, inserting a row into a column with a numeric type
2. A number or date is out of range. The range is determined by the data type. For example, for the unsigned tinyint type, the valid range is numbers from 0 to 255
3. When inserting data, the value of the column is omitted, which does not have a default value and has the NOT NULL attribute
4. The length of the value is outside the specified range. For example, for a column of type CHAR (5), you cannot insert a string of more than 5 characters
5. For types ENUM and SET, there is no inserted or updated value
More details about the features of this mode will be discussed separately in the next chapter.
STRICT_ALL_TABLES
STRICT_ALL_TABLES is completely identical to STRICT_TRANS_TABLES, but the mode already applies to all MySQL tables, not just transactional ones.
Due to the difference in the approaches to the operation of transactional and non-transactional tables, it does not always make sense to use this mode. If this is not yet obvious to you, then in the chapters on strict and non-strict modes 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.
Makes MySQL 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. close 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 data type real 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 does not have a complete 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 SQL mode modes and dive deeper into the problem, then you are welcome to the official documentation http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Work with SQL mode in PHP
In truth, this chapter can hardly be called applied, because in real projects you need to configure it directly on the MySQL server, and not by means of the programming language, therefore the chapter is more theoretical, but for general development it’s good to keep in mind this way.
Most often, a connection to the database occurs through an instance of the PDO class, so we will consider it in detail.
There are two ways to pass special instructions to the database. The first way is passing 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 that with the first three parameters everything is clear, but $ driver_options just gives us the ability to execute queries directly in the database. To set the sql_mode system variable to TRADITIONAL mode, 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 on-the-fly configuration through the setAttribute method;
$db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET sql_mode = 'TRADITIONAL'");
Of course, some of you may object that you can execute queries using the query or exec method, but since the chapter is originally theoretical, I will not focus on this method.
You can read more about PDO 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 the STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and composite TRADITIONAL modes. Already from the name itself it is easy to guess that all input data are checked with special care and in case of violation of any restrictions, an error will inevitably await you.
An error in transactional tables causes a rollback of the transaction (rollback). Even if your requests are not preceded by the start transaction command, then implicitly each request individually will be wrapped in the start transaction and commit commands in any way. This is how all traditional databases work, which applies equally to transactional MySQL tables. From this it follows that, having violated the restriction, rollback is called, which rolls back all the changes.
For non-transactional tables, things are a little more complicated. So, when inserting, updating or deleting several rows, in case of an error only the last action is canceled, instead of a complete 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)
An error is generated in the following cases:
1. The type of inserted data differs from the specified type of column
2. The value for the column that does not have a default value and has the NOT NULL attribute is omitted
3. For numbers and dates, the data is outside the range of acceptable values
4. For rows, the length is exceeded
5. For types ENUM and SET, 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 data for one of the columns is not specified in the insert query, then MySQL will handle this situation in the following order:
1. If the column has a default value, then this value is used and that’s it, otherwise it will go to the next step
2 If the column does not have the NOT NULL parameter, then NULL is assigned and this all ends, otherwise the behavior depends on the sql_mode variable, more precisely, on the strictness 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 undo the last action for non-transactional tables.
For soft mode, a 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 strings except the enum type, an 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 by the null values '0000-00-00 00:00:00'
Lax mode
Hurrah! Finally, we got to the very “mysterious” part of the article, which some cover as some kind of MySQL magic, but, alas, these are just tricks for children's fun. And so, let's go!
Perhaps you should have previously described all the cases for which data validation rules apply, but I decided to do this only now. There are only three of them, but each of them requires a separate consideration.
Data modification requests: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Updating the description of table schemas: ALTER TABLE
Setting the default value (DEFAULT) in the column description
I remind you that in strict mode incorrect data will lead to error generation and data rollback, and in non-strict mode, the value will not be explicitly converted to the correct value and a warning will be generated. Use SHOW WARNINGS to view errors.
Below we will consider 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 allowable 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)
Line processing
Lines longer than the 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 will convert 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, storing values only to 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)
Convert 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 this 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 |
Nulling a column with NOT NULL
The result depends on whether one row or many will be inserted in the INSERT query.
When inserting a single row, an error occurs and the changes are not applied. For multiple insertion - MySQL implicitly converts the default value for this data type.
Updating table schema descriptions: ALTER TABLE
When changing the data type, the restrictions of the new type are imposed on the column, which can lead to an unexpected change in the data itself according to the rules described above.
If a NOT NULL constraint is imposed on a column, then all NULL values are converted to default values for the specified data type of the current column. The default values are described in the chapter "Default values for data types."
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 the default value (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 rightfully be called a real Jedi and get a black belt)))
We take advantage
IGNORE
The IGNORE keyword forces MySQL to enable lax mode for such a query. It can also be used to generate a warning instead of an error in case of 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 type of request is not quite an ideal example, but it was included in order to once again recall that such a thing exists at all. It is used to perform data insertion, or updating in violation of the primary key integrity restriction (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)