一个MySQL的alter convert的案例

一个MySQL的alter convert的案例

最近在做生产和测试表结构巡检的时候发现的这个case。

这里复现并记录下，

代码语言：sql复制

[test] > select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+

[test] > CREATE TABLE `sbt1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `policy_id` bigint(20) unsigned NOT NULL COMMENT '',
  `content` text NOT NULL COMMENT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3;

使用alter convert将字符集改为utf8mb4的
[test] > ALTER TABLE  sbt1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

再次查看表结构，发现content列的类型变成了mediumtext了
[test] > show create table sbt1 \G
*************************** 1. row ***************************
       Table: sbt1
Create Table: CREATE TABLE `sbt1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `policy_id` bigint unsigned NOT NULL,
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


上面的这个case， 如果是sql_mode是严格模式的话，也是一样的效果。

但是下面的这个case，在sql_mode不同的时候，结果就不太一样了。

代码语言：sql复制

create database test2;
use test2;

[test2] > show variables like '%sql_mode%' \G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.01 sec)


[test2] > CREATE TABLE `sbt2` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `policy_id` bigint(20) unsigned NOT NULL COMMENT '',
    ->   `content` varchar(60000) NOT NULL COMMENT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3;
ERROR 1074 (42000): Column length too big for column 'content' (max = 21845); use BLOB or TEXT instead

content太长了，调低点，如下：
[test2] >  CREATE TABLE `sbt2` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `policy_id` bigint(20) unsigned NOT NULL COMMENT '',
    ->   `content` varchar(21810) NOT NULL COMMENT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 3 warnings (0.03 sec)

执行convert,可以看到继续报错了
[test2] > ALTER TABLE  sbt2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ERROR 1074 (42000): Column length too big for column 'content' (max = 16383); use BLOB or TEXT instead

改下sql_mode为宽松模式后再试试
[test2] > set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

可以看到convert执行成功了
[test2] > ALTER TABLE  sbt2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

但是有个warning，提示做了类型转换
[test2] > show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                                          |
+-------+------+--------------------------------------------------+
| Note  | 1246 | Converting column 'content' from VARCHAR to TEXT |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)

[test2] > show create table sbt2 \G
*************************** 1. row ***************************
       Table: sbt2
Create Table: CREATE TABLE `sbt2` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `policy_id` bigint unsigned NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)