linux mysql 永久修改 字符集.docx
《linux mysql 永久修改 字符集.docx》由会员分享，可在线阅读，更多相关《linux mysql 永久修改 字符集.docx（8页珍藏版）》请在冰豆网上搜索。
linuxmysql永久修改字符集
linuxmysql永久修改字符集
1.查找MySQL的cnf文件的位置
find/-iname'*.cnf'-print
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/texmf/web2c/f
/usr/share/texmf/web2c/f
/usr/share/texmf/web2c/f
/usr/share/texmf/tex/xmltex/f
/usr/share/texmf/tex/jadetex/f
/usr/share/doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
2.拷贝f、my-f、my-f、my-innodb-heavy-4G.cnf其中的一个到/etc下，命名为f
cp/usr/share/mysql/my-f/etc/f
3.修改f
vi/etc/f
在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8
4.重新启动MySQL
[root@bogon~]#/etc/rc.d/init.d/mysqlrestart
ShuttingdownMySQL [确定]
StartingMySQL. [确定]
[root@bogon~]#mysql-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1
Serverversion:
5.1.22-rc-community-logMySQLCommunityEdition（GPL）
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
5.查看字符集设置
mysql>showvariableslike'collation_%';
+----------------------+-----------------+
|Variable_name |Value |
+----------------------+-----------------+
|collation_connection|utf8_general_ci|
|collation_database |utf8_general_ci|
|collation_server |utf8_general_ci|
+----------------------+-----------------+
3rowsinset（0.02sec）
mysql>showvariableslike'character_set_%';
+--------------------------+----------------------------+
|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/share/mysql/charsets/|
+--------------------------+----------------------------+
8rowsinset（0.02sec）
参考：
ubuntu10.04mySql启动，停止，重启
启动：
sudo/etc/init.d/mysqlstart
停止：
sudo/etc/init.d/mysqlstop
重启：
sudo/etc/init.d/mysqlrestart
注意：
从windows下拷贝过来的文件在linux下不能直接加入数据库，因为字符集不一样，这样打开即使显示是中文也不能在数据库中显示，要把文件拷贝进linux的另一个文件中转换字符才可以。
MySQL乱码的根源是的MySQL字符集设置不当的问题，本文汇总了有关查看MySQL字符集的命令。
包括查看MySQL数据库服务器字符集、查看MySQL数据库字符集，以及数据表和字段的字符集、当前安装的MySQL所支持的字符集等。
一、查看MySQL数据库服务器和数据库字符集。
mysql>showvariableslike'%char%';
+--------------------------+-------------------------------------+------
|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|D:
\MySQLServer5.0\share\charsets\|......
+--------------------------+-------------------------------------+------
二、查看MySQL数据表（table）的字符集。
mysql>showtablestatusfromsqlstudy_dblike'%countries%';
+-----------+--------+---------+------------+------+-----------------+------
|Name|Engine|Version|Row_format|Rows|Collation|......
+-----------+--------+---------+------------+------+-----------------+------
|countries|InnoDB|10|Compact|11|utf8_general_ci|......
+-----------+--------+---------+------------+------+-----------------+------
三、查看MySQL数据列（column）的字符集。
mysql>showfullcolumnsfromcountries;
+----------------------+-------------+-----------------+--------
|Field|Type|Collation|.......
+----------------------+-------------+-----------------+--------
|countries_id|int（11）|NULL|.......
|countries_name|varchar（64）|utf8_general_ci|.......
|countries_iso_code_2|char
（2）|utf8_general_ci|.......
|countries_iso_code_3|char（3）|utf8_general_ci|.......
|address_format_id|int（11）|NULL|.......
+----------------------+-------------+-----------------+--------
四、查看当前安装的MySQL所支持的字符集。
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|
|cp1251|WindowsCyrillic|cp1251_general_ci|1|
|cp1256|WindowsArabic|cp1256_general_ci|1|
|cp1257|WindowsBaltic|cp1257_general_ci|1|
|binary|Binarypseudocharset|binary|1|
|geostd8|GEOSTD8Georgian|geostd8_general_ci|1|
|cp932|SJISforWindowsJapanese|cp932_japanese_ci|2|
|eucjpms|UJISforWindowsJapanese|eucjpms_japanese_ci|3|
+----------+-----------------------------+---------------------+--------+
以上查看MySQL字符集命令，适用于Windows&Linux。
1.查找MySQL的cnf文件的位置
find/-iname'*.cnf'-print
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/mysql/my-f
/usr/share/texmf/web2c/f
/usr/share/texmf/web2c/f
/usr/share/texmf/web2c/f
/usr/share/texmf/tex/xmltex/f
/usr/share/texmf/tex/jadetex/f
/usr/share/doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
/usr/share/doc/MySQL-server-community-5.1.22/my-f
2.拷贝f、my-f、my-f、my-innodb-heavy-4G.cnf其中的一个到/etc下，命名为f
cp/usr/share/mysql/my-f/etc/f
3.修改f
vi/etc/f
在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8
4.重新启动MySQL
[root@bogon~]#/etc/rc.d/init.d/mysqlrestart
ShuttingdownMySQL [确定]
StartingMySQL. [确定]
[root@bogon~]#mysql-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1
Serverversion:
5.1.22-rc-community-logMySQLCommunityEdition（GPL）
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
5.查看字符集设置
mysql>showvariableslike'collation_%';
+----------------------+-----------------+
|Variable_name |Value |
+----------------------+-----------------+
|collation_connection|utf8_general_ci|
|collation_database |utf8_general_ci|
|collation_server |utf8_general_ci|
+----------------------+-----------------+
3rowsinset（0.02sec）
mysql>showvariableslike'character_set_%';
+--------------------------+----------------------------+
|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/share/mysql/charsets/|
+--------------------------+----------------------------+
8rowsinset（0.02sec）
mysql>
其他的一些设置方法：
修改数据库的字符集
mysql>usemydb
mysql>alterdatabasemydbcharactersetutf-8;
创建数据库指定数据库的字符集
mysql>cr
升级会员 