MySql database uses SQL statements to set encoding, mysql Database SQL statements

MySql database uses SQL statements to set encoding, mysql Database SQL statements
-- View All character codes

Show character set;

-- View the commands for creating a database and the encoding used by the database
Show create database dbtest;

-- View the database encoding:

Show variables like '% char % ';

-- Set character_set_server, set character_set_client, and set character_set_results.
Set character_set_server = utf8; -- the default Character set of the server. This statement can be modified successfully, but it will become invalid after the service is restarted. The fundamental method is to modify and configure the MYSQL file MY. INI, character_set_server = utf8, and configure it to the mysqld field.
Set character_set_client = gbk; -- Character set of the statements from the client. The server uses the character_set_client variable as the character set used in the query sent by the client.
Set character_set_results = gbk; -- character set used to return query results to the client. The character_set_results variable indicates that the server returns 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 database encoding method when creating a database
-- Character set: Specifies the character set used by the database. UTF-8 cannot be written as UTF-8.
-- COLLATE: Specifies the sorting rule of the database character set. The default sorting rule of 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;

-- When creating a table, set the table and field Encoding
Use dbtest;
Drop table if exists tbtest;
Create table tbtest (
Id int (10) auto_increment,
User_name varchar (60) 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 (60) character set utf8 COLLATE utf8_general_ci;


-- View the encoding used by a field:
Select charset (email) FROM tbtest;

How to set the encoding attribute in the mysql table creation statement so that it can display Chinese Characters

The encoding of your mysql database is consistent with that of your script, so you don't have to worry about Chinese garbled characters.
You can use the set names command if no consistency is available.

I changed mysql to UTF-8 encoding. I can't add Chinese characters to SQL statements.

Encoding settings are available for all segments.
Garbled characters must be caused by chaotic coding.

Solution:

Step 1: First Change the database Encoding

First modify your database, if your page uses UTF-8 encoding, then your database encoding also needs to be set to UTF-8, each field needs to be set. to maintain internal and external consistency, you can use the Navicat for MySQL tool, which can be clearly cleared. If there are many table \ fields, you can export SQL statements, then, replace the corresponding encoding in the SQL statement, such as gb2312 with utf8, and then re-create a database. During the creation, select utf8 as the character set and then import the SQL statement, check if all the codes are utf8.

Step 2 program modification

1 \ when reading the database
Mysql_query ("set names utf8 ");
2 \ add a sentence to the header of each page
Header ("content-Type: text/html; charset = UTF-8 ");
3 \ check whether all encoding statements are correct
<Meta http-equiv = "Content-Type" content = "text/html; charset = UTF-8">
4 \ check whether the encoding of the existing text on the page is correct, we recommend that you use editplus in the lower right corner will show UTF-8 or ANSI, to ensure that all files with Chinese open after the display of the album is UTF-8, if not, open all the files and select "document"-"file encoding"-"file encoding (Multi-file)" from the menu. Then select all the files and click OK, change code to UTF-8 OK!

OK. All codes are the same and no garbled characters will appear again. I only recommend two MYSQL tools. One is PHPMYADMIN, and the other is Navicat for MySQL. as for MYSQL-font, it is a little rubbish. Sometimes the database structure displayed is different from the actual one .... in addition, SQLyog officially issued by MYSQL has poor support for encoding and is difficult to control specific encoding. Therefore, it is best not to use SQLyog.