由 emoji 引起的 MySQL 字符集设置一解

MySQL 为了解决 emoji 问题,开始支持 utf8mb4。并且 utf8mb4 也完全兼容 utf8,所以在日后进行相关安装或者设定时完全可以设置字符集为 utf8mb4 了。整理了如何查看 MySQL 字符集的设置情况,字符集相关变量的解释,编译时又怎么设定字符集、字符序,对于只想让线上的数据库的某个表中的某个字段支持 emoji 又该怎么办?两种字符序 general_ci、unicode_ci 选择哪种又比较好。

编译安装指定、查看、配置

编译时指定参数

-DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci

在编译时指定了 -DDEFAULT_CHARSET-DDEFAULT_COLLATION 的情况下,数据库字符集的设置情况

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR   Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

我们看到还有几个变量值是 utf8,此时需要设置 my.cnf

[client]
default-character-set = utf8mb4

重启 MySQL 后,再次进行查看


mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR   Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

字符集相关变量

变量 注解
character_set_client 客户端来源数据使用的字符集
character_set_connection 连接层字符集
character_set_database 当前选中数据库的默认字符集
character_set_results 查询结果字符集
character_set_server 默认的内部操作字符集
character_set_system 系统元数据(字段名等)字符集
以 collation_ 开头的 描述字符序

只让表的某个字段正常存储 emoji

users 表结构

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `email` varchar(120) NOT NULL,
  `password` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

在不全量修改数据库字符集设置和 my.cnf 配置文件情况下,让 name 字段可以存储 emoji 的三板斧。

  • 修改 users
ALTER TABLE `users` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 修改 users.name 字段
ALTER TABLE `users` CHANGE `name` `name` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
  • 链接 MySQL 的服务语言,charset 要设置为 utf8mb4

全量修改数据库的三板斧

  • 修改现有的数据库
  • 修改 my.cnf,变量设置参考上面的「字符集相关变量」
  • 服务端语言建立 MySQL 链接时的设定

general_ci 和 unicode_ci 的选择

  • unicode_ci 的精确度高于 general_ci
  • unicode_ci 的速度慢于 general_ci

个人比较倾向建议使用 unicode_ci

在 stackoverflow 上关于此选择的问答 What's the difference between utf8_general_ci and utf8_unicode_ci

其它相关资料

让 MySQL 支持 emoji 存储

深入Mysql字符集设置

知识共享许可协议
本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。
随喜
支付宝随喜
微信随喜

我也遇到这个坑 后来改成utf8mb4就好了

Get famous on social media! Increase your social media followers at the lowest prices and delivered instantly in less than 24 hours. 100% Safe!, No password (or any account access) required! Get more fans and traffic within hours! Start now at: boostmyfollowers.com

After Westbrook scores on a player, he often gets animated, shouting and showing up opponents, and this series was no exception. NFL Jerseys Wholesale http://www.nfljerseyscheapwholesale.us/

That happened to Senator Sanders yesterday, and I’m calling it out,” she continued. “In what world? You don’t boo folks for that. And I don’t care if it was somebody who I didn’t care about their policies, if they stood up with the Reverend Dr. Martin Luther King Jr. and other heroes and sheroes, we need to shout that out. NFL Jerseys http://www.nfl-jerseys.us.org/

http://www.pandora-officialsite.us/ Pandora Jewelry Official Site Nike Outlet http://www.nikeshoes.us.org/

fcgbamohi,Some really nice stuff on this website, I enjoy it. Nike Outlet Store http://www.nike--outlet.us/

txbbjrcxf,A fascinating discussion is definitely worth comment. I do think that you ought to publish more on this topic, it may not be a taboo qostex,subject but generally folks don't talk about such subjects. To the next! All the best!! Nike Outlet Store http://www.nikeoutletonlineshopping.us/

Priligy Tabletas <a href=http://gemeds.com>kamagra para ellas</a> Generique Viagra Prix Cialis Per Dimagrire Kann Man Viagra Legal Kaufen

It’s not having to walk with a cane or the rehab itself that is the biggest issue for Saban,Jordan though. Travis Scott Air Jordan 1 http://www.travisscottjordan1.us.com/

http://buyalbendazole.us.com/ - buy albendazole

<a href="http://prozac.agency/">online prozac</a>

<a href="http://buybiaxin.team/">biaxin 500 mg</a>

<a href="http://buyazithromycin.company/">where can i buy azithromycin</a> <a href="http://buyatenolol.company/">buy atenolol online</a> <a href="http://buycafergot.company/">buy cafergot</a> <a href="http://valtrex.agency/">daily valtrex no prescription</a> <a href="http://buyindocin.team/">indocin generic</a> <a href="http://synthroid.best/">levothyroxine ordering online</a> <a href="http://prednisone.cc/">prednisone</a>; <a href="http://1stlisinoprilnow.com/">lisinopril 20 mg no prescription</a> <a href="http://buytadacip.team/">tadacip online</a>

<a href="http://levitra.best/">levitra order</a>

http://buyestrace.team/ - buy estrace cream

<a href="http://amoxil.team/">next page</a>

http://tadalafil.surf/ - tadalafil dosage 40 mg

lswwvv,Definitely believe that which you said. Your favourite justification appeared to be on the net the simplest thing to remember of. Nike Element 87 http://www.nikereactelement87.us/

<a href="http://valtrex.rodeo/">online valtrex</a>

<a href="http://sildenafil03.us.com/">sildenafil citrate over the counter</a>

<a href="http://lisinopril.cc/">lisinopril</a>; <a href="http://xenical.rodeo/">where can i buy xenical over the counter</a> <a href="http://buydoxycycline.company/">where to get doxycycline</a> <a href="http://buymetformin.company/">metformin 500mg with out presciption</a>

http://lasix.cc/ - lasix online

http://buydoxycycline.team/ - doxycycline 40 mg

<a href="http://onlineclomid.us.com/">Clomid Online</a> <a href="http://1stprednisonenow.com/">prednisone</a>; <a href="http://buyventolin.company/">buy ventolin inhaler</a> <a href="http://1sttadacipnow.com/">buy tadacip</a> <a href="http://seroquel.team/">buy seroquel online</a>

http://buyacyclovir.company/ - acyclovir online

<a href="http://cialis.surf/">buy cialis canada review</a>

<a href="http://levaquin.team/">levaquin</a>; <a href="http://lasix.yoga/">check out your url</a> <a href="http://lasix.rodeo/">lasix</a>; <a href="http://buyalbuterol.company/">where can i buy albuterol inhaler</a>

<a href="http://buycelebrex.company/">cheap celebrex</a>

http://glucotrol.team/ - glucotrol xl 5mg

<a href="http://1sttetracyclinenow.com/">buy tetracycline online without a prescription from canada</a> <a href="http://clozaril.team/">clozaril</a>; <a href="http://antabuse.cc/">antabuse</a>;

<a href="http://buyelimite.us.com/">cheap elimite</a>

<a href="http://aldactone.team/">aldactone for hair loss in females</a>

Over The Counter Fertility Drugs Amoxicillin Antibiotics In Otitis Media Amoxicillin Used For Dogs <a href=http://ciali20mg.com>cialis 5 mg best price usa</a> Generic Viagra Canada Rx Viagra Langzeitstudie

http://buypropranolol.company/ - propranolol 40 mg

<a href="http://lipitor.team/">find out more</a>

<a href="http://buyindocin.team/">indocin sr 75 mg</a> <a href="http://lasix.agency/">lasix 40mg to buy</a> <a href="http://sildenafil.cc/">sildenafil citrate 100mg tab</a> <a href="http://tofranil.team/">read more</a> <a href="http://1stazithromycinnow.com/">azithromycin</a>; <a href="http://1stalbendazolenow.com/">albenza generic</a> <a href="http://onlinevaltrex.us.com/">valtrex generic online</a> <a href="http://buylisinopril.team/">buy lisinopril</a> <a href="http://buylasix.team/">buy lasix</a> <a href="http://buyamoxicillin.company/">where can i buy amoxicillin online</a> <a href="http://xenical.rodeo/">order xenical online</a>

<a href="http://lisinopril.cc/">where to buy lisinopril</a>

<a href="http://albendazole.us.org/">visit website</a> <a href="http://skelaxin.team/">skelaxin</a>; <a href="http://lisinopril.cc/">lisinopril 20mg tablets</a> <a href="http://buyamitriptyline.us.org/">buy amitriptyline online</a>

http://prednisolone.cc/ - prednisolone

http://levitraprofessional.us.com/ - levitra professional price

http://buyprednisolone.team/ - buy prednisolone

<a href="http://buyelimite.us.com/">where to buy elimite</a>

<a href="http://1stlisinoprilnow.com/">click here</a> <a href="http://mobic.team/">mobic</a>; <a href="http://buyvardenafil.team/">buy vardenafil</a> <a href="http://1stprednisonenow.com/">where can i buy prednisone without prescription</a> <a href="http://1stamoxicillinnow.com/">generic for amoxicillin</a> <a href="http://buypropranolol.us.org/">propranolol</a>; <a href="http://buyyasmin.us.com/">found here</a> <a href="http://zanaflex.team/">buy zanaflex online</a>

<a href="http://buyzithromax.company/">buy zithromax online</a> <a href="http://sildenafil.agency/">where to buy sildenafil citrate</a> <a href="http://cialis.yoga/">cialis</a>; <a href="http://1stprednisonenow.com/">prednisone</a>;

<a href="http://zithromax.cc/">zithromax without prescription</a> <a href="http://doxycycline.cc/">vibramycin 100 mg</a> <a href="http://buytetracycline.team/">tetracycline</a>; <a href="http://buyacyclovir.team/">buy acyclovir</a> <a href="http://azithromycin250mg.us.com/">home page</a> <a href="http://viagrasoft.team/">cheap viagra soft tabs</a> <a href="http://avalide.team/">avalide</a>; <a href="http://synthroid.rodeo/">synthroid</a>; <a href="http://prozac.rodeo/">buy prozac online uk</a> <a href="http://toradol.team/">toradol pain shot</a> <a href="http://buyazithromycin.team/">azithromycin no prescription</a> <a href="http://tofranil.team/">tofranil for anxiety</a> <a href="http://buycafergot.us.org/">ORDER CAFERGOT</a>

<a href="http://xenical.surf/">where can i buy xenical over the counter</a> <a href="http://buyprozac.team/">order prozac online</a>

http://buykamagra.company/ - kamagra gel oral

<a href="http://metformin.rodeo/">going here</a> <a href="http://inderal.team/">buy inderal online</a> <a href="http://genericmotilium.team/">motilium domperidone 10mg</a> <a href="http://levitra.best/">levitra</a>;

<a href="http://tetracycline.yoga/">order tetracycline online</a>

<a href="http://1stretinanow.com/">retin a 0.1</a>

<a href="http://buyventolin.team/">order ventolin online</a>

http://1stcafergotnow.com/ - cafergot & internet pharmacy

<a href="http://get-drugs-now.com/">where can i buy diflucan without a prescription</a>

<a href="http://tadalafil.rodeo/">generic cialis tadalafil uk</a> <a href="http://elocon.team/">elocon</a>; <a href="http://prozac.best/">prozac online</a> <a href="http://prednisone.surf/">prednisone 5 mg</a> <a href="http://buyalbuterol.company/">buy albuterol</a> <a href="http://inderal.team/">inderal la</a>

http://buyatenolol.team/ - buy atenolol online

<a href="http://revia.team/">revia 50 mg</a> <a href="http://buysildenafil.company/">buy sildenafil</a>

<a href="http://buytretinoin.company/">buy tretinoin</a>

http://1stallopurinolnow.com/ - allopurinol generic

Cialis Generico PreР С–Р’В§o <a href=http://buyoxys.com>low cost levitra</a> Discount Legally Cash On Delivery Amoxicilina For Sale Real Inverness Generique Du Viagra Comprar Viagra Cialis Generico

<a href="http://buycialis.team/">buy cialis</a>

http://lisinopril.cc/ - prinivil lisinopril

<a href="http://buynolvadex.us.org/">buy nolvadex</a> <a href="http://vpxl.team/">learn more here</a> <a href="http://buycolchicine.company/">buy colchicine</a> <a href="http://cheapvardenafil365.us.com/">purchase vardenafil</a> <a href="http://prozac.best/">prozac 40mg</a> <a href="http://levaquin.team/">levaquin levofloxacin</a> <a href="http://lasix.surf/">lasix no preiscription</a> <a href="http://avana.team/">super avana</a> <a href="http://cialis.yoga/">cealis</a>; <a href="http://synthroid.yoga/">generic synthroid</a> <a href="http://buytrazodone.team/">trazodone 300 mg</a> <a href="http://buyglucophage.us.com/">glucophage</a>; <a href="http://buydiflucan.company/">buy diflucan</a> <a href="http://buyprednisone.us.org/">Buy Prednisone</a> <a href="http://cialis.rodeo/">cialis</a>; <a href="http://prozac.yoga/">helpful hints</a>

<a href="http://buyalbendazole.team/">buy albendazole on line</a>

<a href="http://celebrex247.us.org/">Celebrex Without A Prescription</a>

<a href="http://buyglucophage.us.com/">generic glucophage</a>

<a href="http://tadalafil.yoga/">more hints</a>

<a href="http://avana.team/">super avana</a>

<a href="http://levitra.yoga/">levitra online</a>

<a href="http://buyalbendazole.us.com/">buy albendazole</a> <a href="http://1stfurosemidenow.com/">furosemide for sale</a> <a href="http://cymbalta.team/">cymbalta 60 mg capsules</a> <a href="http://synthroid.best/">synthroid</a>; <a href="http://levitraprofessional.us.com/">levitra professional price</a> <a href="http://buyazithromycin.company/">buy azithromycin</a> <a href="http://buycephalexin.team/">buy cephalexin 500mg</a> <a href="http://antabuse.cc/">antabuse substitute</a>

<a href="http://buytadacip.team/">buy tadacip online</a>

<a href="http://buytetracycline.company/">tetracycline</a>; <a href="http://lisinopril.cc/">lisinopril</a>; <a href="http://1ststratteranow.com/">atomoxetine price</a> <a href="http://lexapro.yoga/">generic for cipralex</a> <a href="http://prednisolone.agency/">buy prednisolone 5mg without prescription uk</a>

<a href="http://sildenafil.yoga/">sildenafil from india</a>

<a href="http://buyallopurinol.company/">buy allopurinol</a>

<a href="http://valtrex.rodeo/">buy valtrex online without a prescription</a>

<a href="http://methotrexate.team/">methotrexate without a prescription</a>

<a href="http://1stcephalexinnow.com/">cephalexin 250mg capsules</a>

<a href="http://buyalbenza.team/">buy albenza</a>

http://buybenicar.us.com/ - benicar online

<a href="http://buylasix.team/">online lasix without prescription</a>

<a href="http://cialis03.us.com/">cialis</a>; <a href="http://buyxenical.team/">buy xenical</a> <a href="http://1stprednisolonenow.com/">cheap prednisolone</a> <a href="http://cleocin.us.com/">CLEOCIN</a>; <a href="http://cardura.team/">cardura</a>; <a href="http://buystromectol.team/">cheap stromectol</a> <a href="http://buykamagra.team/">buy kamagra</a> <a href="http://buymotilium.us.com/">buy motilium</a> <a href="http://buyadvair.team/">buy advair diskus</a> <a href="http://lipitor.team/">lipitor</a>; <a href="http://buyviagra.team/">buy viagra</a> <a href="http://lexapro.company/">lexapro</a>; <a href="http://buyhydrochlorothiazide.company/">buy hydrochlorothiazide</a> <a href="http://provera.team/">buy provera pills with mastercard</a> <a href="http://buypropecia.company/">can you buy propecia over the counter</a>

http://zithromax.cc/ - zithromax

<a href="http://buyacyclovir.company/">where can i buy acyclovir online</a>

<a href="http://buycolchicine.team/">buy colchicine</a>

<a href="http://plavix.team/">plavix</a>; <a href="http://elimite.team/">how much does permethrin cost</a>

<a href="http://lexapro.yoga/">where can i buy cipralex</a>