MySQL ERROR 1406 Data too long for column:长度按字符算,关掉严格模式就变成悄悄截断,加宽列跨过 255 字节要重建表

  insert into s(v) values ('abcdef') -> DataError: (1406, "Data too long for column 'v' at row 1")
  insert into s(v) values ('ok'),('abcdefg'),('ok') -> DataError: (1406, "Data too long for column 'v' at row 2")
  表里的行数: 0(整条语句都没写进去)
  5 个英文字母: char_length=5 字节(utf8mb4)=5 -> ok
  5 个汉字: char_length=5 字节(utf8mb4)=15 -> ok
  5 个 emoji: char_length=5 字节(utf8mb4)=20 -> ok
  6 个汉字: char_length=6 字节(utf8mb4)=18 -> DataError: (1406, "Data too long for column 'v' at row 1")

  默认 @@sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  set session sql_mode = '' 之后 insert 'abcdefghij' -> ok,影响行数 1
  show warnings: (('Warning', 1265, "Data truncated for column 'v' at row 1"),)
  表里存的是: (('abcde',),)(被截成前 5 个字符)
  只留 STRICT_TRANS_TABLES 再试一次 -> DataError: (1406, "Data too long for column 'v' at row 1")

  tinyint 放 200 -> DataError: (1264, "Out of range value for column 'a' at row 1")
  int unsigned 放 -1 -> DataError: (1264, "Out of range value for column 'b' at row 1")
  create table (v varchar(20000)) -> OperationalError: (1074, "Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead")
  create table (v varchar(16383)) -> ok
  create table (v varchar(16384)) -> OperationalError: (1074, "Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead")
  text 放 70000 个英文字母 -> DataError: (1406, "Data too long for column 't' at row 1")
  text 放 65535 个英文字母 -> ok
  text 放 21846 个汉字(65538 字节)-> DataError: (1406, "Data too long for column 't' at row 1")
  mediumtext 放 70000 个英文字母 -> ok

  各行 char_length: ((1, 5), (2, 17), (3, 20))
  alter table sh modify v varchar(10) -> DataError: (1265, "Data truncated for column 'v' at row 2")
  事先自查:select count(*) from sh where char_length(v) > 10 = 2
  表结构没变: `v` varchar(20) DEFAULT NULL,

  表里 200000 行
  varchar(10) -> varchar(20)(utf8mb4 最大字节数 40 -> 80) ALGORITHM=INPLACE: ok,1 ms
  varchar(20) -> varchar(40)(utf8mb4 最大字节数 80 -> 160) ALGORITHM=INSTANT: OperationalError: (1845, 'ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.'),0 ms
  varchar(60) -> varchar(63)(utf8mb4 最大字节数 240 -> 252) ALGORITHM=INPLACE: ok,1 ms
  varchar(63) -> varchar(100)(utf8mb4 最大字节数 252 -> 400) ALGORITHM=INPLACE: OperationalError: (1846, 'ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.'),0 ms
  varchar(63) -> varchar(100)(utf8mb4 最大字节数 252 -> 400) ALGORITHM=COPY: ok,271 ms
  现在的列定义: ['`v10` varchar(20) DEFAULT NULL,', '`v60` varchar(100) DEFAULT NULL,']
  (varchar 最大字节数 ≤ 255 时用 1 字节存长度,> 255 用 2 字节;改的前后落在不同的一档,就不能原地改,必须重建整张表)