Shulou(Shulou.com)06/01 Report--

Dealing with mysql coding problem

We often encounter some people insert Chinese into the MySQL database, but when the select comes out, it is garbled.

Look up the library, table character set command

1. View all character sets supported by the database

Show character set (show char set)

two。 Check the current status, which of course includes the settings of the character set

Status (/ s)

3. View the system character set settings, including all character set settings

Show variables like'% char%'

4. View character set settings in the data table

Show full columns from tablename

You can also view the table structure

Show create table tablename\ G

5. View database encoding (database structure)

Show create database dbname;// creates the character set specified by the database

2. Specify the character set when creating the library table column (to be unified)

1. Server level

When installing MySQL, you can set the default encoding format of the server, or you can modify my.ini. If you modify the character_set_server=utf8 in [mysqld], you can set the value of character_set_server.

two。 Database level

Create database dbname default character set utf8

Note that if you do not specify a default character set, the system will set it according to the value of character_set_database

3. Table level

Create table dbname.tbname (id varchar (20) not null, name vharchar (20)) engine=innoDB default charset=utf8

Note: the default character set of the defined table is utf8, and even if character_set_database is gbk, the columns of the table are utf8. If the default character set of the table is not defined, it will be set according to the value of character_set_database

4. Column level

Create table db1.tb2 (id varchar (20) not null, name varchar (20) character set utf8)

Note: if you do not specify a column character set, use the table character set, and if you do, use the specified one.

In the figure: show create table db1.tb2// is to view the table structure

III. Modify the character set

1. Modify the values of character_set_connection, character_set_client and character_set_results

For a connection, you can use the

Set names utf8 to use the above three values to change to utf8

Set name' charset_name'; is equivalent to

Set character_set_client = charset_name

SET character_set_results = charset_name

SET character_set_connection = charset_name

two。 Modify the character_set_database field (i.e. change the database character set)

Alter database db_name default character set charset_name

3. Modify the character_set_server field

The easiest way is to directly change the [mysqld] field in the my.ini configuration file, add character-set-server=gbk, and then restart mysqld, which can be changed to the character set you want.

4. Modify the character set of a table

Alter table tbname default character set charsetname

Alter table tb1 default character set utf8

5. Modify the character set of a column

Alter table tbname modify age varchar (30) character set utf8

Summary:

1. Set up unified coding for building database, table and field.

2. PHP as the mysqle server client, connection coding set names utf8/gbk

3. Set the encoding of the data returned by php to the browser. (Content-Type,header (),)

Header ("Content-type:text/html;charset=utf-8")

4. The encoding saved by the Php file itself (file encoding, set through a text editor)

5. Set the code when installing mysql. (my.ini can be modified after installation)

Scan Wechat and follow the official account.

Video of irregularly sharing information

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.