Description:
A column which is CHAR(2) can contain more characters than 2. See the how to repeat instructions. Firstly the char column is char(20) - enough to hold "andrey" (in cyrillic). Then the column is altered to be CHAR(2) which produces truncation warnings as it should be but at the end there are 3 chars in the column. SHOW CREATE TABLE still says that the column is CHAR(2). If the column is made larger - again CHAR(20) an ASCII string is inserted "123456" and then ALTER to CHAR(2) there is _no_ truncation warning and the the second value in the column is stil "123456"
How to repeat:
mysql> create table t567(c char(20) character set utf8);
Query OK, 0 rows affected (0.05 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t567 values('андрей');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t567;
+--------------+
| c |
+--------------+
| андрей |
+--------------+
1 row in set (0.00 sec)
mysql> alter table t567 modify c char(2) character set utf8;
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'c' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t567;
+--------+
| c |
+--------+
| анд |
+--------+
1 row in set (0.00 sec)
mysql> show create table t567;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------+
| t567 | CREATE TABLE `t567` (
`c` char(2) character set utf8 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select char_length(c) from t567;
+----------------+
| char_length(c) |
+----------------+
| 3 |
+----------------+
1 row in set (0.01 sec)
mysql> select length(c) from t567;
+-----------+
| length(c) |
+-----------+
| 6 |
+-----------+
1 row in set (0.01 sec)
mysql> alter table t567 modify c char(20) character set utf8;
Query OK, 1 row affected (0.17 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t567 values("123456");
Query OK, 1 row affected (0.06 sec)
mysql> alter table t567 modify a char(2) character set utf8;
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select a, char_length(a), length(a) from t567;
+--------+----------------+-----------+
| a | char_length(a) | length(a) |
+--------+----------------+-----------+
| анд | 3 | 6 |
| 123456 | 6 | 6 |
+--------+----------------+-----------+
2 rows in set (0.07 sec)
Description: A column which is CHAR(2) can contain more characters than 2. See the how to repeat instructions. Firstly the char column is char(20) - enough to hold "andrey" (in cyrillic). Then the column is altered to be CHAR(2) which produces truncation warnings as it should be but at the end there are 3 chars in the column. SHOW CREATE TABLE still says that the column is CHAR(2). If the column is made larger - again CHAR(20) an ASCII string is inserted "123456" and then ALTER to CHAR(2) there is _no_ truncation warning and the the second value in the column is stil "123456" How to repeat: mysql> create table t567(c char(20) character set utf8); Query OK, 0 rows affected (0.05 sec) mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> insert into t567 values('андрей'); Query OK, 1 row affected (0.00 sec) mysql> select * from t567; +--------------+ | c | +--------------+ | андрей | +--------------+ 1 row in set (0.00 sec) mysql> alter table t567 modify c char(2) character set utf8; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'c' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t567; +--------+ | c | +--------+ | анд | +--------+ 1 row in set (0.00 sec) mysql> show create table t567; +-------+------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------+ | t567 | CREATE TABLE `t567` ( `c` char(2) character set utf8 default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select char_length(c) from t567; +----------------+ | char_length(c) | +----------------+ | 3 | +----------------+ 1 row in set (0.01 sec) mysql> select length(c) from t567; +-----------+ | length(c) | +-----------+ | 6 | +-----------+ 1 row in set (0.01 sec) mysql> alter table t567 modify c char(20) character set utf8; Query OK, 1 row affected (0.17 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t567 values("123456"); Query OK, 1 row affected (0.06 sec) mysql> alter table t567 modify a char(2) character set utf8; Query OK, 2 rows affected (0.14 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select a, char_length(a), length(a) from t567; +--------+----------------+-----------+ | a | char_length(a) | length(a) | +--------+----------------+-----------+ | анд | 3 | 6 | | 123456 | 6 | 6 | +--------+----------------+-----------+ 2 rows in set (0.07 sec)