Brief analysis of the MySQL character set causing database recovery errors

Importing data with incorrect MySQL character set encoding will prompt an error. This is the same as inserting data. If the saved data is different from the MySQL encoding, there will definitely be problems with importing garbled characters or inserting data being lost. Let's take a look at an example.

Database recovery error: Due to character set issues, the original database default encoding is latin1, and the encoding of the newly backed up database is utf8, which leads to recovery errors.

[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/11x-B-2014-06-18.sql 
ERROR 1064 (42000) at line 292: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[caption id=\"attachment_271\" align=\"aligncenter\" width=\"300\"]<a href=\"ht' at line 1

Repair method (not tested):

[root@Test ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' --default-character-set=latin1 t4x < /tmp/11x-B-2014-06-18.sql 
MySQL
-- MySQL dump 10.13 Distrib 5.5.37, for Linux (x86_64)
--
-- Host: localhost Database: t4x
-- ------------------------------------------------------
--Server version 5.5.37-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `t4x`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET utf8 */;
--
-- Table structure for table `wp_baidusubmit_sitemap`
--
DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_baidusubmit_sitemap` (
 `sid` int(11) NOT NULL AUTO_INCREMENT,
 `url` varchar(255) NOT NULL DEFAULT '',
 `type` tinyint(4) NOT NULL,
 `create_time` int(10) NOT NULL DEFAULT '0',
 `start` int(11) DEFAULT '0',
 `end` int(11) DEFAULT '0',
 `item_count` int(10) unsigned DEFAULT '0',
 `file_size` int(10) unsigned DEFAULT '0',
 `lost_time` int(10) unsigned DEFAULT '0',
 PRIMARY KEY (`sid`),
 KEY `start` (`start`),
 KEY `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
0
1
[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/t4x-B-2014-06-17.sql 
ERROR 1064 (42000) at line 295: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''i' at line 1

MySQL

--MySQL dump 10.11
--
-- Host: localhost Database: t4x
-- ------------------------------------------------------
--Server version 5.0.95-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- 
-- Current Database: `t4x`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `t4x`;
--
-- Table structure for table `wp_baidusubmit_sitemap`
--
DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_baidusubmit_sitemap` (
 `sid` int(11) NOT NULL auto_increment,
 `url` varchar(255) NOT NULL default '',
 `type` tinyint(4) NOT NULL,
 `create_time` int(10) NOT NULL default '0',
 `start` int(11) default '0',
 `end` int(11) default '0',
 `item_count` int(10) unsigned default '0',
 `file_size` int(10) unsigned default '0',
 `lost_time` int(10) unsigned default '0',
 PRIMARY KEY (`sid`),
 KEY `start` (`start`),
 KEY `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Character set related:

MySQL

mysql>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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql>set names gbk;
mysql>show variables like '%character_set%';
--------------------------------------------- 
| 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql>system cat /etc/my.cnf | grep default #Client sets the character set client below default-character-set=gbk
mysql>show variables like '%character_set%';
--------------------------------------------- 
| Variable_name | Value |
--------------------------------------------- 
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql> system cat /etc/my.cnf|grep character-set-server #Client sets the character set mysqld below character-set-server = cp1250 
mysql> show variables like '%character_set%';
-------------------------- -------------------------------------------- 
| Variable_name | Value |
-------------------------- -------------------------------------------- 
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | cp1250 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | cp1250 |
| character_set_system | utf8 |
| character_sets_dir | /byrd/service/mysql/5.6.26/share/charsets/ |
-------------------------- -------------------------------------------- 
8 rows in set (0.00 sec)

Some other setting methods:

Modify the character set of the database

  mysql>use mydb
  mysql>alter database mydb character set utf-8;

Create a database and specify the character set of the database

  mysql>create database mydb character set utf-8;

Modify through the configuration file:

Modify /var/lib/mysql/mydb/db.opt

default-character-set=latin1
default-collation=latin1_swedish_ci

for

default-character-set=utf8
default-collation=utf8_general_ci

Restart MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart

Modify via the MySQL command line:

mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)

Check:

mysql> 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/share/mysql/charsets/ |
--------------------------------------------- 
8 rows in set (0.03 sec)
mysql> show variables like 'collation_%';
---------------------- ----------------- 
| Variable_name | Value |
---------------------- ----------------- 
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
---------------------- ----------------- 
3 rows in set (0.04 sec)

Summarize

The above is all the content of this article about briefly analyzing the problem of MySQL character set causing database recovery errors. I hope it will be helpful to everyone. If you have any questions, please leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!

You may also be interested in:

How to modify the database encoding (database character set) and the character encoding of the table in MySQL
Sharing of MySQL character set and database engine modification methods
When DW is linked to the MySQL database, how to solve the problem of garbled characters in Chinese when creating a character set
How to unify the character set on an existing mysql database

<<: Vue encapsulates a TodoList example and implements the application of browser local cache

>>: Linux system to view CPU, machine model, memory and other information