0

The column definition is as below:

`code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL

I want to alter another table(option)'s code field as same definition:

ALTER TABLE `option`  MODIFY `code`  varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ERROR 1406 (22001): Data too long for column 'code' at row 1

And another example:

show create table mytest;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nasdaq_100 | CREATE TABLE `mytest` (
  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `code` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

To alter the code definition more simple:

ALTER TABLE `mytest`  MODIFY `code`  text DEFAULT NULL;

No error info output ,but can't alter the the column. Show table mytest with show create table mytest,the column definition never changed!

How to fix then?

2
  • 2
    You may want to verify that there are no values in the column wider than 15 characters: SELECT * FROM option ORDER BY LENGTH(code) DESC LIMIT 25;
    – matigo
    Aug 29, 2021 at 15:14
  • code is already text so why do you want to change it and where
    – nbk
    Aug 29, 2021 at 16:35

1 Answer 1

1

I tested:

mysql> insert into `option` set code='supercalifragilisticexpialidocious';

mysql> ALTER TABLE `option`  MODIFY `code`  varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ERROR 1406 (22001): Data too long for column 'code' at row 1

The issue is that MySQL's default sql mode is strict, which prevents you from truncating data accidentally by doing alters like this one.

You can "fix" this behavior by temporarily disabling strict mode:

mysql> set sql_mode='';

mysql> ALTER TABLE `option`  MODIFY `code`  varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
Query OK, 1 row affected, 1 warning (0.02 sec)

But keep in mind you will partially lose some data by doing this:

mysql> select * from `option`;
+----+-----------------+------+
| id | code            | name |
+----+-----------------+------+
|  1 | supercalifragil | NULL |
+----+-----------------+------+

I would recommend you first inspect all the rows where the code is currently longer than your 15 character limit you want to use, and decide if it's okay to truncate them. You may want to change them to another value first, or else decide to alter the column to support strings longer than 15 characters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.