MySQL查看与修改字符集的方法实例教程
一、查看字符集
1.查看MYSQL数据库服务器和数据库字符集
方法一：showvariableslike'%character%';
方法二：showvariableslike'collation%';
mysql>showvariableslike'%character%'; +--------------------------+--------------------------------------+ |Variable_name|Value| +--------------------------+--------------------------------------+ |character_set_client|utf8| |character_set_connection|utf8| |character_set_database|utf8| |character_set_filesystem|binary| |character_set_results|utf8| |character_set_server|utf8| |character_set_system|utf8| |character_sets_dir|/usr/local/mysql5535/share/charsets/| +--------------------------+--------------------------------------+ 8rowsinset(0.00sec) 方法一：
mysql>showvariableslike'collation%'; +----------------------+-----------------+ |Variable_name|Value| +----------------------+-----------------+ |collation_connection|utf8_general_ci| |collation_database|utf8_general_ci| |collation_server|utf8_general_ci| +----------------------+-----------------+ 3rowsinset(0.00sec) 方法二：
2.查看MYSQL所支持的字符集
showcharset;
mysql>showcharset; +----------+-----------------------------+---------------------+--------+ |Charset|Description|Defaultcollation|Maxlen| +----------+-----------------------------+---------------------+--------+ |big5|Big5TraditionalChinese|big5_chinese_ci|2| |dec8|DECWestEuropean|dec8_swedish_ci|1| |cp850|DOSWestEuropean|cp850_general_ci|1| |hp8|HPWestEuropean|hp8_english_ci|1| |koi8r|KOI8-RRelcomRussian|koi8r_general_ci|1| |latin1|cp1252WestEuropean|latin1_swedish_ci|1| |latin2|ISO8859-2CentralEuropean|latin2_general_ci|1| |swe7|7bitSwedish|swe7_swedish_ci|1| |ascii|USASCII|ascii_general_ci|1| |ujis|EUC-JPJapanese|ujis_japanese_ci|3| |sjis|Shift-JISJapanese|sjis_japanese_ci|2| |hebrew|ISO8859-8Hebrew|hebrew_general_ci|1| |tis620|TIS620Thai|tis620_thai_ci|1| |euckr|EUC-KRKorean|euckr_korean_ci|2| |koi8u|KOI8-UUkrainian|koi8u_general_ci|1| |gb2312|GB2312SimplifiedChinese|gb2312_chinese_ci|2| |greek|ISO8859-7Greek|greek_general_ci|1| |cp1250|WindowsCentralEuropean|cp1250_general_ci|1| |gbk|GBKSimplifiedChinese|gbk_chinese_ci|2| |latin5|ISO8859-9Turkish|latin5_turkish_ci|1| |armscii8|ARMSCII-8Armenian|armscii8_general_ci|1| |utf8|UTF-8Unicode|utf8_general_ci|3| |ucs2|UCS-2Unicode|ucs2_general_ci|2| |cp866|DOSRussian|cp866_general_ci|1| |keybcs2|DOSKamenickyCzech-Slovak|keybcs2_general_ci|1| |macce|MacCentralEuropean|macce_general_ci|1| |macroman|MacWestEuropean|macroman_general_ci|1| |cp852|DOSCentralEuropean|cp852_general_ci|1| |latin7|ISO8859-13Baltic|latin7_general_ci|1| |utf8mb4|UTF-8Unicode|utf8mb4_general_ci|4| |cp1251|WindowsCyrillic|cp1251_general_ci|1| |utf16|UTF-16Unicode|utf16_general_ci|4| |cp1256|WindowsArabic|cp1256_general_ci|1| |cp1257|WindowsBaltic|cp1257_general_ci|1| |utf32|UTF-32Unicode|utf32_general_ci|4| |binary|Binarypseudocharset|binary|1| |geostd8|GEOSTD8Georgian|geostd8_general_ci|1| |cp932|SJISforWindowsJapanese|cp932_japanese_ci|2| |eucjpms|UJISforWindowsJapanese|eucjpms_japanese_ci|3| +----------+-----------------------------+---------------------+--------+ 39rowsinset(0.00sec) 查看MYSQL所支持的字符集
3.查看库的字符集
语法：showdatabasestatusfrom库名like 表名;
mysql>showcreatedatabaseshiyan\G ***************************1.row*************************** Database:shiyan CreateDatabase:CREATEDATABASE`shiyan`/*!40100DEFAULTCHARACTERSETgbk*/ 1rowinset(0.00sec)
4.查看表的字符集
语法：showtablestatusfrom库名like 表名;
mysql>showtablestatusfromclass_7like'test_info';
mysql>showtablestatusfromclass_7like'test_info'; +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- |Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_leate_time|Update_time|Check_time|Collation|Checksum| +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- |test_info|InnoDB|10|Compact|10|1638|17-12-0519:01:55|NULL|NULL|utf8_general_ci|NULL| +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- 1rowinset(0.00sec) 查看表的字符集
5.查看表中所有列的字符集
语法：showfullcolumnsfrom表名;
mysql>showfullcolumnsfromtest_info;
mysql>showfullcolumnsfromtest_info; +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ |Field|Type|Collation|Null|Key|Default|Extra|Privileges|Comment| +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ |id|int(3)|NULL|NO|PRI|NULL||select,insert,update,references|| |name|char(12)|utf8_general_ci|YES||NULL||select,insert,update,references|| |dorm|char(10)|utf8_general_ci|YES||NULL||select,insert,update,references|| |addr|char(12)|utf8_general_ci|YES||未知||select,insert,update,references|| |score|int(3)|NULL|YES||NULL||select,insert,update,references|| +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 5rowsinset(0.00sec) 所有列的字符集
二、设置字符集
设置字符集一般有两种方法，一种是在创建表的时候设置字符集，另一种是表建成之后修改字符集。
1.创建时指定字符集
创建库的时候指定字符集：
语法：createdatabase库名defaultcharacterset=字符集；
createdatabasedb2defaultcharacterset=utf8
创建表的时候指定字符集：
语法：createtable表名（属性）defaultcharacterset=字符集；
mysql>createtabletest1(idint(6),namechar(10))defaultcharacterset='gbk'; QueryOK,0rowsaffected(0.39sec)
2.修改字符集
修改全局字符集
/*建立连接使用的编码*/ setcharacter_set_connection=utf8; /*数据库的编码*/ setcharacter_set_database=utf8; /*结果集的编码*/ setcharacter_set_results=utf8; /*数据库服务器的编码*/ setcharacter_set_server=utf8; setcharacter_set_system=utf8; setcollation_connection=utf8; setcollation_database=utf8; setcollation_server=utf8; 修改全局字符集
修改库的字符集
语法：alterdatabase库名defaultcharacterset字符集;
alterdatabaseshiyandefaultcharactersetgbk;
mysql>showcreatedatabaseshiyan\G ***************************1.row*************************** Database:shiyan CreateDatabase:CREATEDATABASE`shiyan`/*!40100DEFAULTCHARACTERSETutf8*/ 1rowinset(0.00sec) mysql>alterdatabaseshiyandefaultcharactersetgbk; QueryOK,1rowaffected(0.00sec) mysql>showcreatedatabaseshiyan\G ***************************1.row*************************** Database:shiyan CreateDatabase:CREATEDATABASE`shiyan`/*!40100DEFAULTCHARACTERSETgbk*/ 1rowinset(0.00sec)
修改表的字符集
语法：altertable表名converttocharacterset字符集;
altertabletest1converttocharactersetutf8;
mysql>showcreatetabletest1\G ***************************1.row*************************** Table:test1 CreateTable:CREATETABLE`test1`( `id`int(6)DEFAULTNULL, `name`char(10)DEFAULTNULL )ENGINE=InnoDBDEFAULTCHARSET=gbk#原字符集 1rowinset(0.00sec) mysql>altertabletest1converttocharactersetutf8; QueryOK,0rowsaffected(0.58sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetabletest1\G ***************************1.row*************************** Table:test1 CreateTable:CREATETABLE`test1`( `id`int(6)DEFAULTNULL, `name`char(10)DEFAULTNULL )ENGINE=InnoDBDEFAULTCHARSET=utf8#修改后的字符集 1rowinset(0.00sec) 修改列表的字符集
修改字段的字符集
语法：altertable表名modify字段名字段属性charactersetgbk；
altertabletest1modifynamechar(10)charactersetgbk;
mysql>showfullcolumnsfromtest1; +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ |Field|Type|Collation|Null|Key|Default|Extra|Privileges|Comment| +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ |id|int(6)|NULL|YES||NULL||select,insert,update,references|| |name|char(10)|utf8_general_ci|YES||NULL||select,insert,update,references|| +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 2rowsinset(0.01sec) mysql>altertabletest1modifynamechar(10)charactersetgbk; QueryOK,0rowsaffected(0.58sec) Records:0Duplicates:0Warnings:0 mysql>showfullcolumnsfromtest1; +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ |Field|Type|Collation|Null|Key|Default|Extra|Privileges|Comment| +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ |id|int(6)|NULL|YES||NULL||select,insert,update,references|| |name|char(10)|gbk_chinese_ci|YES||NULL||select,insert,update,references|| +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ 2rowsinset(0.01sec) 修改字段的字符集
总结
到此这篇关于MySQL查看与修改字符集的文章就介绍到这了,更多相关MySQL查看与修改字符集内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。