在mysql中如何存储emoji表情,如果mysql是utf-8编码,存入表情符出错我们如何解决,下面我们来介绍一下.
utf8的数据库,存入表情符,会出错,代码如下:
Incorrect string value: '\xF0\x9F\x98\x84\xF0\x9F...' for column 'content'
错误的解决办法:
- 4byteUnicodecharactersaren'tyetwidelyused,sonoteveryapplicationouttherefullysupportsthem.MySQL5.5worksfinewith4bytecharacterswhenproperlyconfigured–checkifyourothercomponentscanworkwiththemaswell.
- Here'safewotherthingstocheckout:
- Makesureallyourtables'defaultcharactersetsandtextfieldsareconvertedtoutf8mb4,inadditiontosettingtheclient&servercharactersets,e.g.ALTERTABLEmytablecharset=utf8mb4,MODIFYCOLUMNtextfield1VARCHAR(255)CHARACTERSETutf8mb4,MODIFYCOLUMNtextfield2VARCHAR(255)CHARACTERSETutf8mb4;andsoon.
- Ifyourdataisalreadyintheutf8characterset,itshouldconverttoutf8mb4inplacewithoutanyproblems.Asalways,backupyourdatabeforetrying!
- Alsomakesureyourapplayersetsitsdatabaseconnections'charactersettoutf8mb4.Double-checkthisisactuallyhappening–ifyou'rerunninganolderversionofyourchosenframework'smysqlclientlibrary,itmaynothavebeencompiledwithutf8mb4supportanditwon'tsetthecharsetproperly.Ifnot,youmayhavetoupdateitorcompileityourself.//开源软件:phpfensi.com
- Whenviewingyourdatathroughthemysqlclient,makesureyou'reonamachinethatcandisplayemoji,andrunaSETNAMESutf8mb4beforerunninganyqueries.
- Onceeverylevelofyourapplicationcansupportthenewcharacters,youshouldbeabletousethemwithoutanycorruption.
总结就是:表结构改为支持4字节的unicode,数据库连接也用这个字符集哦,证明是可行的,如果别的地方不支持,可以考虑去掉这些字符:
- Since4-byteUTF-8sequencesalwaysstartwiththebytes0xF0-0xF7,thefollowingshouldwork:
- $str=preg_replace('/[\xF0-\xF7].../s','',$str);
- Alternatively,youcouldusepreg_replaceinUTF-8modebutthiswillprobablybeslower:
- $str=preg_replace('/[\x{10000}-\x{10FFFF}]/u','',$str);
- Thisworksbecause4-byteUTF-8sequencesareusedforcodepointsinthesupplementaryUnicodeplanesstartingfrom0x10000.
