複数行をINSERTした時に列の長さを超えたデータと正常なデータをINSERTした
場合の結果は、SQL_MODEによって違う。

    以下の条件によっても結果は違う事に注意

MyISAM
InnoDB
STRICT_ALL_TABLES
STRICT_TRANS_TABLES


mysql> SELECT @@session.sql_mode;
+---------------------+
| @@session.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.01 sec)


mysql> CREATE TABLE string_test (
-> comment varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc string_test;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

string_test


mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

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

mysql>


mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
ERROR 1406 (22001): Data too long for column 'comment' at row 1
mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

strict

Comments are closed.

Post Navigation