mysql 将一张表的汉字字段的首字母借助中间表添加到表中

临时表

use test3;
drop table if exists py;
create table py(
id int auto_increment primary key ,
capital varchar(10)
);

向临时表插入首字母和id

insert into test3.py (id, capital)
(select id,
ELT(INTERVAL(CONV(HEX(left(CONVERT(name USING GBK),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,
0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q', 'R','S','T','W','X','Y','Z') as PY
from test3.t_car_simple_model);

更新首字母

update test3.t_car_simple_model, test3.py set test3.t_car_simple_model.capital=test3.py.capital where test3.t_car_simple_model.id=test3.py.id;