Emoji字符正在搞乱我们建立的加载系统,我正在寻找一个简单的短期解决方案。
是一个Java加载程序,使用JDBC执行MySQL命令,结构如下:
LOAD DATA
LOCAL INFILE `filepath`
REPLACE INTO TABLE `SOME_TABLE`
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' ESCAPED BY ''
LINES TERMINATED BY 'n'
(`col1`,...,`coln`)
SOME_TABLE有ENGINE=InnoDB DEFAULT CHARSET=utf8
.
我们正在运行MySQL 5.6.22.
多年来一直工作得很好,但最近我们加载的文件开始偶尔出现非bmp字符(碰巧是表情符号)和load DATA LOCAL INFILE…命令抛出如下异常:
java.sql.SQLException: Incorrect string value: 'xF0x9Dx93x9C' for column 'fieldm' at row 3004
我明白长期的解决方案是我们需要把桌子移到CHARSET=utf8mb4
。但是,此时的表非常大,转换并不容易。也有VARCHAR(255)
字段索引,这些需要转换为VARCHAR(191)
[以适应最大键长767],或者我们需要转到DYNAMIC行格式并设置innodb_large_prefix=true
。
我们正在寻找一个短期的解决方案,直到我们有时间和资源迁移到utfmb4。
在短期内,简单地丢弃具有非bmp(表情符号)字符的行是可以的。但是,LOAD DATA LOCAL INFILE filepath REPLACE ...
不会跳过错误的行,它会使整个文件失败。
LOAD DATA LOCAL INFILE filepath REPLACE ...
之前删除非bmp(表情符号)行。但是,我在想一定有一些方法可以在MySQL中做到这一点,而不必引入那种预过滤器。
有没有人有任何想法的一个简单的方法,让MySQL简单地跳过行,有非bmp(表情符号)数据?
***** UPDATE *****看起来使用CONVERT可能是短期的解决方案。这样做会将表情符号替换为"????"
LOAD DATA
LOCAL INFILE `filepath`
REPLACE INTO TABLE `SOME_TABLE`
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' ESCAPED BY ''
LINES TERMINATED BY 'n'
(`col1`,`col2`,`col3`,@q, ..., `coln`)
SET `col4` = CONVERT(CONVERT(@q USING utf8mb4) USING utf8);
有人看出这有什么问题吗?
为了存储表情符号,必须始终使用utf8mb4,而不是utf8。
解决191索引问题的捷径(可能)是升级到5.7。在这里,您可以保留255 和的索引。
只有某些列将持有表情符号,对吗?转换这些列。(同一表中的不同列可以有不同的字符集和/或排序规则)