mysqlの文字セット統一設定の問題
最近1つのプロジェクトをして、過程の中でデータベースの文字化けしが発生して、phpmyadminの中からどのように設定するか分かりません.行きましょう!!文字セット設定が一致していないに違いないと思いますが、php符号化は正しいし、utf-8の文字セット設定を採用しているのでmysql文字セット設定の問題に違いありません.またphpmyadminで半日設定してもだめです.おばあちゃんの、いっそphpmyadminを使わないで、本当に骨が折れる!!
直接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。
-
-
-
- Liunx MySQL ：
- 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.
-
- ：
-
-
- 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)