ay msql 笔记 Mysql 字符集 【10】

时间：2017年04月08日 | 作者 : aaronyang | 分类 : 数据库 | 浏览: 1460次 | 评论 0 人

====================www.ayjs.net       杨洋    wpfui.com        ayui      ay  aaronyang==============

查看所有支持的字符集

show character set

big5Big5 Traditional Chinesebig5_chinese_ci2

dec8DEC West Europeandec8_swedish_ci1

cp850DOS West Europeancp850_general_ci1

hp8HP West Europeanhp8_english_ci1

koi8rKOI8-R Relcom Russiankoi8r_general_ci1

latin1cp1252 West Europeanlatin1_swedish_ci1

latin2ISO 8859-2 Central Europeanlatin2_general_ci1

swe77bit Swedishswe7_swedish_ci1

asciiUS ASCIIascii_general_ci1

ujisEUC-JP Japaneseujis_japanese_ci3

sjisShift-JIS Japanesesjis_japanese_ci2

hebrewISO 8859-8 Hebrewhebrew_general_ci1

tis620TIS620 Thaitis620_thai_ci1

euckrEUC-KR Koreaneuckr_korean_ci2

koi8uKOI8-U Ukrainiankoi8u_general_ci1

gb2312GB2312 Simplified Chinesegb2312_chinese_ci2

greekISO 8859-7 Greekgreek_general_ci1

cp1250Windows Central Europeancp1250_general_ci1

gbkGBK Simplified Chinesegbk_chinese_ci2

latin5ISO 8859-9 Turkishlatin5_turkish_ci1

armscii8ARMSCII-8 Armenianarmscii8_general_ci1

utf8UTF-8 Unicodeutf8_general_ci3

ucs2UCS-2 Unicodeucs2_general_ci2

cp866DOS Russiancp866_general_ci1

keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1

macceMac Central Europeanmacce_general_ci1

macromanMac West Europeanmacroman_general_ci1

cp852DOS Central Europeancp852_general_ci1

latin7ISO 8859-13 Balticlatin7_general_ci1

utf8mb4UTF-8 Unicodeutf8mb4_general_ci4

cp1251Windows Cyrilliccp1251_general_ci1

utf16UTF-16 Unicodeutf16_general_ci4

utf16leUTF-16LE Unicodeutf16le_general_ci4

cp1256Windows Arabiccp1256_general_ci1

cp1257Windows Balticcp1257_general_ci1

utf32UTF-32 Unicodeutf32_general_ci4

binaryBinary pseudo charsetbinary1

geostd8GEOSTD8 Georgiangeostd8_general_ci1

cp932SJIS for Windows Japanesecp932_japanese_ci2

eucjpmsUJIS for Windows Japaneseeucjpms_japanese_ci3

gb18030China National Standard GB18030gb18030_chinese_ci4

查看information_schema.character_sets表可以显示所有的字符集和该字符集默认的校对规则

mysql的字符集包括了字符集character和校对规则 colloation 两个概念。字符集是用来定义mysql存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系。mysql支持30多种字符集和70多种校对规则。比如查看 字符集gbk的校对规则:

show collation like 'gbk%'

查看当前服务器的字符集和校对规则。

 show variables like 'character_set_server';

show variables like 'collation_server'

====================www.ayjs.net       杨洋    wpfui.com        ayui      ay  aaronyang=======请不要转载谢谢了。=========

对于数据库级的字符集，如果没有办法重建数据库，可以在my.cnf文件中使用character-set-server = utf8(要重启数据库服务)，对于之前已经存在数据库，此参数不产生影响。如果之前的数据库A是latin1，在A中创建的表默认还是latin1，除非指定DEFAULT CHARSET！然而对于新建的数据库，则继承 my.cnf文件中使用character-set-server = utf8！

[root@rac3 ~]# more /etc/my.cnf 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

old_passwords=1

character-set-server = utf8 

关闭数据库服务，并重新启动！

[root@rac3 ~]# mysqladmin shutdown

[root@rac3 ~]# mysqld_safe &

[1] 15102

[root@rac3 ~]# Starting mysqld daemon with databases from /var/lib/mysql

[root@rac3 ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%server%';

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| character_set_server | utf8            | 

| collation_server     | utf8_general_ci | 

| server_id            | 0               | 

+----------------------+-----------------+

3 rows in set (0.00 sec)

--重新建立数据库，查看其默认字符集：为utf8

mysql> create database yql;

Query OK, 1 row affected (0.00 sec)

mysql> use yql;

Database changed

新建表的默认字符集和数据库的默认字符集一致！

mysql> create table t(id int);

Query OK, 0 rows affected (0.00 sec)

mysql> show create table t;

+-------+-------------------------------------------------------------------------------------+

| Table | Create Table                                                                        |

+-------+-------------------------------------------------------------------------------------+

| t     | CREATE TABLE `t` (

  `id` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 

+-------+-------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show create database yql \G;  

*************************** 1. row ***************************

       Database: yql

Create Database: CREATE DATABASE `yql` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

ERROR: 

No query specified

推荐您阅读更多有关于“mysql，”的文章

• 上一篇：ay msql 笔记 Mysql 存储引擎 【9】
• 下一篇：ay msql 笔记 memory存储引擎【11】

猜你喜欢

• Access denied for user 'ODBC'@'localhost' (using password: NO) 的解决方法2019-11-08
• ay msql 笔记 flush privilges【15】2017-04-12
• ay msql 笔记 存储过程 游标 循环 【14】2017-04-11
• ay msql 笔记 日志【13】2017-04-10
• ay msql 笔记 MERGE存储引擎【12】2017-04-09
• ay msql 笔记 memory存储引擎【11】2017-04-09
• ay msql 笔记 Mysql 存储引擎 【9】2017-04-08
• ay msql 笔记--SQL服务器模式 【8】2017-04-08
• ay msql 笔记 Linux RPM 注意事项【8】2017-04-06
• ay msql 笔记-日期函数【6】2017-04-06