Monday, June 3, 2019

MySQL: Data too long error


If a string value is too long to insert into a column, we may see this error:

ERROR 1406 (22001): Data too long for column ......

Sometimes this is not the expected behavior. We may just want MySQL to be able to truncate the data and insert the data with a warning.

The behavior is controlled by the setting of SQL Mode. We can check the current SQL Mode by running command:

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)


You can also check the global SQL Mode by running:

mysql> SELECT @@GLOBAL.sql_mode;

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.

In a script, we may temporary disable the STRICT mode before doing a insert and enable it after, if the data we want to insert may contain data longer than a column can accept:

SET SESSION sql_mode = '';
INSERT ...
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

No comments:

 
Get This <