mysql 数据表中文转拼音 sql中文转拼音 SQL中文转拼音首字母大写

这里以城市列表为例

目录

1.导入基础数据

2.创建函数

3. 执行语句

---

1.导入基础数据

     map_city.sql

原始图如下：

      t_base_pinyin  基础数据对照表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_base_pinyin
-- ----------------------------
DROP TABLE IF EXISTS `t_base_pinyin`;
CREATE TABLE `t_base_pinyin`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pin_yin_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `code_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 397 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_base_pinyin
-- ----------------------------
INSERT INTO `t_base_pinyin` VALUES (1, 'a', '20319');
INSERT INTO `t_base_pinyin` VALUES (2, 'ai', '20317');
INSERT INTO `t_base_pinyin` VALUES (3, 'an', '20304');
INSERT INTO `t_base_pinyin` VALUES (4, 'ang', '20295');
INSERT INTO `t_base_pinyin` VALUES (5, 'ao', '20292');
INSERT INTO `t_base_pinyin` VALUES (6, 'ba', '20283');
INSERT INTO `t_base_pinyin` VALUES (7, 'bai', '20265');
INSERT INTO `t_base_pinyin` VALUES (8, 'ban', '20257');
INSERT INTO `t_base_pinyin` VALUES (9, 'bang', '20242');
INSERT INTO `t_base_pinyin` VALUES (10, 'bao', '20230');
INSERT INTO `t_base_pinyin` VALUES (11, 'bei', '20051');
INSERT INTO `t_base_pinyin` VALUES (12, 'ben', '20036');
INSERT INTO `t_base_pinyin` VALUES (13, 'beng', '20032');
INSERT INTO `t_base_pinyin` VALUES (14, 'bi', '20026');
INSERT INTO `t_base_pinyin` VALUES (15, 'bian', '20002');
INSERT INTO `t_base_pinyin` VALUES (16, 'biao', '19990');
INSERT INTO `t_base_pinyin` VALUES (17, 'bie', '19986');
INSERT INTO `t_base_pinyin` VALUES (18, 'bin', '19982');
INSERT INTO `t_base_pinyin` VALUES (19, 'bing', '19976');
INSERT INTO `t_base_pinyin` VALUES (20, 'bo', '19805');
INSERT INTO `t_base_pinyin` VALUES (21, 'bu', '19784');
INSERT INTO `t_base_pinyin` VALUES (22, 'ca', '19775');
INSERT INTO `t_base_pinyin` VALUES (23, 'cai', '19774');
INSERT INTO `t_base_pinyin` VALUES (24, 'can', '19763');
INSERT INTO `t_base_pinyin` VALUES (25, 'cang', '19756');
INSERT INTO `t_base_pinyin` VALUES (26, 'cao', '19751');
INSERT INTO `t_base_pinyin` VALUES (27, 'ce', '19746');
INSERT INTO `t_base_pinyin` VALUES (28, 'ceng', '19741');
INSERT INTO `t_base_pinyin` VALUES (29, 'cha', '19739');
INSERT INTO `t_base_pinyin` VALUES (30, 'chai', '19728');
INSERT INTO `t_base_pinyin` VALUES (31, 'chan', '19725');
INSERT INTO `t_base_pinyin` VALUES (32, 'chang', '19715');
INSERT INTO `t_base_pinyin` VALUES (33, 'chao', '19540');
INSERT INTO `t_base_pinyin` VALUES (34, 'che', '19531');
INSERT INTO `t_base_pinyin` VALUES (35, 'chen', '19525');
INSERT INTO `t_base_pinyin` VALUES (36, 'cheng', '19515');
INSERT INTO `t_base_pinyin` VALUES (37, 'chi', '19500');
INSERT INTO `t_base_pinyin` VALUES (38, 'chong', '19484');
INSERT INTO `t_base_pinyin` VALUES (39, 'chou', '19479');
INSERT INTO `t_base_pinyin` VALUES (40, 'chu', '19467');
INSERT INTO `t_base_pinyin` VALUES (41, 'chuai', '19289');
INSERT INTO `t_base_pinyin` VALUES (42, 'chuan', '19288');
INSERT INTO `t_base_pinyin` VALUES (43, 'chuang', '19281');
INSERT INTO `t_base_pinyin` VALUES (44, 'chui', '19275');
INSERT INTO `t_base_pinyin` VALUES (45, 'chun', '19270');
INSERT INTO `t_base_pinyin` VALUES (46, 'chuo', '19263');
INSERT INTO `t_base_pinyin` VALUES (47, 'ci', '19261');
INSERT INTO `t_base_pinyin` VALUES (48, 'cong', '19249');
INSERT INTO `t_base_pinyin` VALUES (49, 'cou', '19243');
INSERT INTO `t_base_pinyin` VALUES (50, 'cu', '19242');
INSERT INTO `t_base_pinyin` VALUES (51, 'cuan', '19238');
INSERT INTO `t_base_pinyin` VALUES (52, 'cui', '19235');
INSERT INTO `t_base_pinyin` VALUES (53, 'cun', '19227');
INSERT INTO `t_base_pinyin` VALUES (54, 'cuo', '19224');
INSERT INTO `t_base_pinyin` VALUES (55, 'da', '19218');
INSERT INTO `t_base_pinyin` VALUES (56, 'dai', '19212');
INSERT INTO `t_base_pinyin` VALUES (57, 'dan', '19038');
INSERT INTO `t_base_pinyin` VALUES (58, 'dang', '19023');
INSERT INTO `t_base_pinyin` VALUES (59, 'dao', '19018');
INSERT INTO `t_base_pinyin` VALUES (60, 'de', '19006');
INSERT INTO `t_base_pinyin` VALUES (61, 'deng', '19003');
INSERT INTO `t_base_pinyin` VALUES (62, 'di', '18996');
INSERT INTO `t_base_pinyin` VALUES (63, 'dian', '18977');
INSERT INTO `t_base_pinyin` VALUES (64, 'diao', '18961');
INSERT INTO `t_base_pinyin` VALUES (65, 'die', '18952');
INSERT INTO `t_base_pinyin` VALUES (66, 'ding', '18783');
INSERT INTO `t_base_pinyin` VALUES (67, 'diu', '18774');
INSERT INTO `t_base_pinyin` VALUES (68, 'dong', '18773');
INSERT INTO `t_base_pinyin` VALUES (69, 'dou', '18763');
INSERT INTO `t_base_pinyin` VALUES (70, 'du', '18756');
INSERT INTO `t_base_pinyin` VALUES (71, 'duan', '18741');
INSERT INTO `t_base_pinyin` VALUES (72, 'dui', '18735');
INSERT INTO `t_base_pinyin` VALUES (73, 'dun', '18731');
INSERT INTO `t_base_pinyin` VALUES (74, 'duo', '18722');
INSERT INTO `t_base_pinyin` VALUES (75, 'e', '18710');
INSERT INTO `