Using SQL statements to manipulate MySQL character encoding

--View all character encodings

SHOW CHARACTER SET;

--View the instructions for creating the database and view the encoding used by the database
Show CREATE Database dbtest;

--View Database encoding:

Show variables like '%char% ';

--Set the Character_set_server, set Character_set_client, and set Character_set_results
Set character_set_server = UTF8; --The default character set for the server. This statement can be used to modify the success, but will be invalidated after restarting the service. The fundamental approach is to modify the configuration of the MySQL file My.ini,character_set_server=utf8, configured under the Mysqld field.
Set character_set_client = GBK; --the character set of the statement from the client. The server uses the Character_set_client variable as the character set used in queries sent by the client.
Set character_set_results = GBK; --the character set used to return query results to the client. The character_set_results variable instructs the server to return the query result to the character set used by the client. Includes result data, such as column values and result metadata, such as column names.

--Set the encoding of the database when creating the database
--CHARACTER Set: Specifies the character set used by the database, UTF8 cannot be written utf-8
--COLLATE: Specifies the collation of the database character set, and the default collation for UTF8 is utf8_general_ci (viewed through show character set)
Drop database if EXISTS dbtest;
Create database dbtest CHARACTER SET UTF8 COLLATE utf8_general_ci;

--Modify Database encoding
ALTER DATABASE dbtest CHARACTER SET GBK COLLATE gbk_chinese_ci;
ALTER DATABASE dbtest CHARACTER SET UTF8 COLLATE utf8_general_ci;

--Set table, field encoding when creating a table
Use dbtest;
drop table if exists tbtest;
CREATE TABLE Tbtest (
ID Int (TEN) auto_increment,
User_name varchar (CHARACTER) SET GBK COLLATE gbk_chinese_ci,
Email varchar (60),
PRIMARY Key (ID)
) CHARACTER SET UTF8 COLLATE utf8_general_ci;

--Modify Table encoding
ALTER TABLE tbtest character set UTF8 COLLATE utf8_general_ci;
--Modify field encoding
ALTER TABLE tbtest MODIFY email VARCHAR CHARACTER SET UTF8 COLLATE utf8_general_ci;


--View the encoding used for a field:
SELECT CHARSET (email) from tbtest;

Using SQL statements to manipulate MySQL character encoding