The best solution to Mysql Chinese random code problem

Generally speaking, MySQL has the following factors:

1.server's own character set problem, for example, is still at latin1
2. Language setting of table (including character and collation)
3. Setting the connection language of the client program (such as php)

utf8 encoding is strongly recommended for this purpose! Because utf8 is compatible with all the characters in the world!

1. Avoid creating databases and tables with messy Chinese codes and viewing the coding methods

1. When creating the database:

CREATE DATABASE `test`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

2. When you build your watch

CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Once these three are set up, there is basically no problem, that is, the database and the table are built using the same coding format.
But if you already have libraries and tables you can query them in the following way.

1. Check the default encoding format:

mysql> show variables like "%char%";
+--------------------------+---------------+
| Variable_name | Value |
+--------------------------+---------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+-------------+

Note: To confirm the previous 2, set names utf8 and set names gbk can be used to set the default encoding format;

Executing SET NAMES utf8 has the same effect as setting it simultaneously as follows:

SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';

2. Check the encoding format of test database:

mysql> show create database test;
+------------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------+------------------------------------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+------------------------------------------------------------------------------------------------+

3. Check the encoding format of yjdb database:

mysql> show create table yjdb;
| yjdb | CREATE TABLE `yjdb` (
`sn` int(5) NOT NULL AUTO_INCREMENT,
`type` varchar(10) NOT NULL,
`brc` varchar(6) NOT NULL,
`teller` int(6) NOT NULL,
`telname` varchar(10) NOT NULL,
`date` int(10) NOT NULL,
`count` int(6) NOT NULL,
`back` int(10) NOT NULL,
PRIMARY KEY (`sn`),
UNIQUE KEY `sn` (`sn`),
UNIQUE KEY `sn_2` (`sn`)
) ENGINE=MyISAM AUTO_INCREMENT=1826 DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC |

2. Avoid the problem of confusing Chinese characters in the imported data

1. Save the data encoding format as ES58en-8
Set the default code to utf8:

set names utf8;

Set the database db_name to utf8 by default:

ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Setting table tb_name default encoding is utf8:

ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Import:

LOAD DATA LOCAL INFILE 'C:\\utf8.txt' INTO TABLE yjdb;

2. Save the data encoding format as ansi(i.e. GBK or GB2312)
Set the default encoding to gbk:

CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

0

Set the default encoding of database db_name to gbk:

CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1

Setting table tb_name Default encoding is gbk:

CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2

Import:

LOAD DATA LOCAL INFILE 'C:\\gbk.txt' INTO TABLE yjdb;

Note:
(1)UTF8 do not import gbk and gbk do not import UTF8;
(2) UTF8 is not supported under dos;

3. Solve the problem of disorderly code in the web page

Set the site code to utf-8, which is compatible with all characters in the world.
If the site is too old to change the simplified Chinese character setting, GBK is recommended. The difference between GBK and GB2312 is that GBK can display more characters than GB2312.

1. Edit/etc my. cnf, in [mysql] join default_character_set = utf8;
2. When writing Connection URL, add ? useUnicode = true & characterEncoding = utf - 8 refs.
3. Add a "set names utf8" or "set names gbk" command to the page code to tell MySQL to use all the links
utf8 or gbk;