LSX-blog

Feed Rss

【转载】查看mysql字符集MySQL 乱码的根源是的 MySQL 字符

12.01.2011, MYSQL, by .

ubuntu 10.04 mySql 启动,停止,重启

启动:sudo /etc/init.d/mysql start
停止:sudo /etc/init.d/mysql stop
重启:sudo /etc/init.d/mysql restart

注意:从windows下拷贝过来的文件在linux下不能直接加入数据库,因为字符集不一样,这样打开即使显示是中文也不能在数据库中显示,要把文件拷贝进linux的另一个文件中转换字符才可以。

MySQL 乱码的根源是的 MySQL 字符集设置不当的问题,本文汇总了有关查看 MySQL 字符集的命令。包括查看 MySQL 数据库服务器字符集、查看 MySQL 数据库字符集,以及数据表和字段的字符集、当前安装的 MySQL 所支持的字符集等。

一、查看 MySQL 数据库服务器和数据库字符集。

mysql> show variables like ‘%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:\MySQL Server 5.0\share\charsets\ |……
+————————–+————————————-+——
二、查看 MySQL 数据表(table) 的字符集。

mysql> show table status from sqlstudy_db like ‘%countries%’;
+———–+——–+———+————+——+—————–+——
| Name | Engine | Version | Row_format | Rows | Collation |……
+———–+——–+———+————+——+—————–+——
| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |……
+———–+——–+———+————+——+—————–+——
三、查看 MySQL 数据列(column)的字符集。

mysql> show full columns from countries;
+———————-+————-+—————–+——–
| 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> show charset;
mysql> show char set;
+———-+—————————–+———————+——–+
| Charset | Description | Default collation | Maxlen |
+———-+—————————–+———————+——–+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | 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-large.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/texmf/web2c/texmf.cnf
/usr/share/texmf/web2c/mktex.cnf
/usr/share/texmf/web2c/fmtutil.cnf
/usr/share/texmf/tex/xmltex/xmltexfmtutil.cnf
/usr/share/texmf/tex/jadetex/jadefmtutil.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-large.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-small.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-medium.cnf
/usr/share/doc/MySQL-server-community-5.1.22/my-huge.cnf

2. 拷贝 small.cnf、my-medium.cnf、my-huge.cnf、my-innodb-heavy-4G.cnf其中的一个到/etc下,命名为my.cnf
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

3. 修改my.cnf
vi /etc/my.cnf
在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8

4.重新启动MySQL
[root@bogon ~]# /etc/rc.d/init.d/mysql restart
Shutting down MySQL [ 确定 ]
Starting MySQL. [ 确定 ]
[root@bogon ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

5.查看字符集设置
mysql> show variables like ‘collation_%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3 rows in set (0.02 sec)
mysql> show variables like ‘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/ |
+————————–+—————————-+
8 rows in set (0.02 sec)
mysql>

其他的一些设置方法:

修改数据库的字符集
mysql>use mydb
mysql>alter database mydb character set utf-8;
创建数据库指定数据库的字符集
mysql>create database mydb character set utf-8;

通过配置文件修改:
修改/var/lib/mysql/mydb/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci

default-character-set=utf8
default-collation=utf8_general_ci
重起MySQL:
[root@bogon ~]# /etc/rc.d/init.d/mysql restart

通过MySQL命令行修改:
mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)
查看:
mysql> show variables like ‘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/ |
+————————–+—————————-+
8 rows in set (0.03 sec)
mysql> show variables like ‘collation_%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3 rows in set (0.04 sec)

————————————————————————-
【知识性文章转载】
MYSQL 字符集问题

MySQL的字符集支持(Character Set Support)有两个方面:
字符集(Character set)和排序方式(Collation)。
对于字符集的支持细化到四个层次:
服务器(server),数据库(database),数据表(table)和连接(connection)。
1.MySQL默认字符集
MySQL对于字符集的指定可以细化到一个数据库,一张表,一列,应该用什么字符集。
但是,传统的程序在创建数据库和数据表时并没有使用那么复杂的配置,它们用的是默认的配置,那么,默认的配置从何而来呢? (1)编译MySQL 时,指定了一个默认的字符集,这个字符集是 latin1;
(2)安装MySQL 时,可以在配置文件 (my.ini) 中指定一个默认的的字符集,如果没指定,这个值继承自编译时指定的;
(3)启动mysqld 时,可以在命令行参数中指定一个默认的的字符集,如果没指定,这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集;
(4)当创建一个新的数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;
(5)当选定了一个数据库时,character_set_database 被设定为这个数据库默认的字符集;
(6)在这个数据库里创建一张表时,表默认的字符集被设定为 character_set_database,也就是这个数据库默认的字符集;
(7)当在表内设置一栏时,除非明确指定,否则此栏缺省的字符集就是表默认的字符集;
简单的总结一下,如果什么地方都不修改,那么所有的数据库的所有表的所有栏位的都用
latin1 存储,不过我们如果安装 MySQL,一般都会选择多语言支持,也就是说,安装程序会自动在配置文件中把
default_character_set 设置为 UTF-8,这保证了缺省情况下,所有的数据库的所有表的所有栏位的都用 UTF-8 存储。
2.查看默认字符集(默认情况下,mysql的字符集是latin1(ISO_8859_1)
通常,查看系统的字符集和排序方式的设定可以通过下面的两条命令:
mysql> SHOW VARIABLES LIKE ‘character%’;
+————————–+———————————+
| Variable_name | Value |
+————————–+———————————+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:”mysql-5.0.37″share”charsets” |
+————————–+———————————+
mysql> SHOW VARIABLES LIKE ‘collation_%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3.修改默认字符集
(1) 最简单的修改方法,就是修改mysql的my.ini文件中的字符集键值,
如 default-character-set = utf8
character_set_server = utf8
修改完后,重启mysql的服务,service mysql restart
使用 mysql> SHOW VARIABLES LIKE ‘character%’;查看,发现数据库编码均已改成utf8
+————————–+———————————+
| 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:”mysql-5.0.37″share”charsets” |
+————————–+———————————+
(2) 还有一种修改字符集的方法,就是使用mysql的命令
mysql> SET character_set_client = utf8 ;

【转载】查看mysql字符集MySQL 乱码的根源是的 MySQL 字符 有 11 条回应

  1. My family every time say that I am wasting my time here at net, but I know I am getting familiarity everyday by reading such
    pleasant posts.

    回复
  2. I got this web page from my pal who told me regarding this web site and now this
    time I am visiting this site and reading very informative posts
    at this time.

    回复
  3. An interesting discussion is worth comment.
    I believe that you should write more on this issue, it may not be a taboo matter but
    generally folks don’t speak about such topics.
    To the next! Cheers!!

    回复
  4. Good day I am so thrilled I found your site, I really found you by error, while
    I was researching on Aol for something else, Anyways I am here now and would just like to say
    thank you for a remarkable post and a all round entertaining blog (I also love
    the theme/design), I don’t have time to read through it all at the minute but I have saved it and also added your RSS feeds, so when I have time
    I will be back to read a lot more, Please do keep up the
    awesome job.

    回复
  5. Hey there! Would you mind if I share your blog with my myspace group?
    There’s a lot of people that I think would really enjoy
    your content. Please let me know. Cheers

    回复
  6. Hey There. I found your blog using msn. This is an extremely well written article.
    I will make sure to bookmark it and return to read more
    of your useful information. Thanks for the post. I’ll definitely return.

    回复
  7. When some one searches for his required thing, so he/she needs to be available that in detail, thus that thing
    is maintained over here.

    回复
  8. Very energetic blog, I enjoyed that bit. Will there be a part 2?

    回复
  9. Hi, this weekend is good in support of me, as this point in time i am reading this great
    educational article here at my house.

    回复
  10. Excellent way of telling, and pleasant post to
    get information concerning my presentation topic,
    which i am going to convey in academy.

    回复
  11. how often can i take is

    8ee0 enter site inexpensive is

    回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注