Unified Character Set setting of mysql

I recently created a project with database garbled characters. I don't know how to set it in phpmyadmin. Let me go !! I thought it was definitely because the character set settings were inconsistent, php encoding was correct, and UTF-8 character set was used, so it must be a problem with mysql Character Set settings. If phpmyadmin is used for a long time, it still does not work. Grandma, you don't need phpmyadmin. It's really hard !!
I opened the mysql console directly. Haha, I hope the following operations will be helpful to you. Remember to come back and make progress together !!!
1. view the MySQL database server and database character set.
 
Mysql> show variables like '% char % ';
+ -------------------------- + ------------------------------------- + ------
| Variable_name | Value | ......
+ -------------------------- + ------------------------------------- + ------
| Character_set_client | utf8 |... -- client Character Set
| Character_set_connection | utf8 | ......
| Character_set_database | utf8 | ...... -- database Character Set
| Character_set_filesystem | binary | ......
| Character_set_results | utf8 | ......
| Character_set_server | utf8 | ...... -- server Character Set
| Character_set_system | utf8 | ......
| Character_sets_dir | D: \ MySQL Server 5.0 \ share \ charsets \ | ......
+ -------------------------- + ------------------------------------- + ------
2. view the character set of the MySQL DATA 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 | ......
+ ----------- + -------- + --------- + ------------ + ------ + ----------------- + ------
3. view the character set of the MySQL DATA 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 | .......
+ ---------------------- + ------------- + ----------------- + --------
4. view the character sets supported by currently installed 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 |
+ ---------- + ----------------------------- + --------------------- + -------- +
The preceding MySQL character set command is applicable to Windows and Linux.
 

 
Modify the MySQL character set in Liunx:
1. Find the cnf File Location of MySQL
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. Copy small. cnf, my-medium.cnf, my-huge.cnf, one of the my-innodb-heavy-4G.cnf to/etc, named my. cnf
Cp/usr/share/mysql/my-medium.cnf/etc/my. cnf
 
3. modify my. cnf
Vi/etc/my. cnf
Add under [client]
Default-character-set = utf8
Add under [mysqld]
Default-character-set = utf8
 
4. Restart MySQL
[Root @ bogon ~] #/Etc/rc. d/init. d/mysql restart
Shutting down MySQL [OK]
Starting MySQL. [OK]
[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.
 
Other settings:
 
Modify the character set of a Database
Mysql> use mydb
Mysql> alter database mydb character set UTF-8;
Creates a database and specifies the character set of the database.
Mysql> create database mydb character set UTF-8;
 
Modify the configuration file:
Modify/var/lib/mysql/mydb/db. opt
Default-character-set = latin1
Default-collation = latin1_swedish_ci
Is
Default-character-set = utf8
Default-collation = utf8_general_ci
Restart MySQL:
[Root @ bogon ~] #/Etc/rc. d/init. d/mysql restart
 
Use the MySQL command line to modify:
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)
 
Author "You_Can"