Method 1:
First, view the database encoding format
1 |
mysql> show variables like ‘character_set_database‘ ; |
Second, look at the data table encoding format
1 |
mysql> show create table <表名>; |
Iii. specifying the character set of a database when creating a database
1 |
mysql> create database <数据库名> character set utf8; |
Iv. specifying the encoding format of a data table when creating a data table
12345 |
create table tb_books (
name varchar
(45)
not null
,
price
double not null
,
bookCount
int not null
,
author
varchar
(45)
not null )
default charset = utf8;
|
V. Modifying the encoding format of the database
1 |
mysql> alter database <数据库名> character set utf8; |
Vi. Modifying data table encoding format
1 |
mysql> alter table <表名> character set utf8; |
Vii. Modifying the field encoding format
123 |
MYSQL> alter table < table name > Change < Field name > < Field name > < type > character set UTF8; MYSQL> alter Table user change username username varchar () character set utf8 not null |
Method 2:
Change the configuration file under Mac system:
Edit, join:
[Client]
Default-character-set=utf8
[MySQL]
Default-character-set=utf8
[Mysqld]
Character-set-server=utf8
That
It is important to note that the [MYSQLD] label must be Character-set-server=utf8, or MySQL will not start
Show variables like ' character_set_% '; +--------------------------+-----------------------------------------------------------+ | variable_name | Value | +--------------------------+-----------------------------------------------------------+ | character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | Latin1 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/local/mysql-5.7.17-macos10.12-x86_64/share/charsets/| +--------------------------+-----------------------------------------------------------+ |
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/local/mysql-5.7.17-macos10.12-x86_64/share/charsets/| +--------------------------+-----------------------------------------------------------+ |
Contrast before, change success
When connecting MySQL database with Pycharm, be careful to add charset= "UTF8", that is:
CNX = Pymysql.connect (user= "root", passwd= "root", host= "localhost", db= "db_name", charset= "UTF8")
Otherwise Chinese cannot import database
----Clear the Data sheet
TRUNCATE TABLE tenement_message;
-----Find the MySQL folder
which MySQL
MySQL Change UTF8 encoding method