Teach you how to solve the error when storing Chinese characters in MySQL database

Teach you how to solve the error when storing Chinese characters in MySQL database

1. Problems encountered

When storing Chinese characters in the database, I encountered this problem:

Cause: java.sql.SQLException: Incorrect string value: '\xE6\x9F\xAF\xE5\x8D\x97' for column 'user_name' at row 1

insert image description here

2. Analyze the problem

This is due to a problem with the database design. When the database was created, the default was selected directly without changing it to utf-8. Later, I tried to modify it manually but it still didn't work .

I tried changing the default fields of the database and table to utf8, but Chinese characters still cannot be stored.

insert image description here
insert image description here

3. The real problem

insert image description here

The real problem is that when you open the table information, you can see the existence of latin1 . This is because even if we manually modify the field type later, we still cannot modify it. Just like above, even if we modify it, the table information is still not changed. The real solution is: 1. Either we re-create the database and set it to utf8 when creating it (a bit difficult), 2. Or the following solution (not so easy if there are many fields involved), export and modify the SQL statement, and then re-execute the SQL statement.

4. Solution

Export our database as a sql file:

/*
SQLyog Enterprise v12.08 (64 bit)
MySQL - 5.7.31 : Database - data_test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`data_test` /*!40100 DEFAULT CHARACTER SET latin1*/;

USE `data_test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) CHARACTER SET latin1 NOT NULL COMMENT 'Account',
  `password` varchar(50) CHARACTER SET latin1 NOT NULL COMMENT 'password',
  `user_state` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '1' COMMENT 'Status, logical deletion',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

/*Data for the table `user` */

LOCK TABLES `user` WRITE;

insert into `user`(`user_id`,`user_name`,`password`,`user_state`) values ​​(1,'keafmd','keafmd','1'),(3,'21312321','123','0'),(11,'213','213','1'),(12,'keafmd','666','0');

UNLOCK TABLES;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

As mentioned above, we found latin1, so we will replace all latin1 with utf8 .

The replaced sql file:

/*
SQLyog Enterprise v12.08 (64 bit)
MySQL - 5.7.31 : Database - data_test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`data_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `data_test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT 'Account',
  `password` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT 'password',
  `user_state` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT 'Status, logical deletion',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

LOCK TABLES `user` WRITE;

insert into `user`(`user_id`,`user_name`,`password`,`user_state`) values ​​(1,'keafmd','keafmd','1'),(3,'21312321','123','0'),(11,'213','213','1'),(12,'keafmd','666','0');

UNLOCK TABLES;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Select Execute SQL script and select the modified SQL file

insert image description here

Now check the table information

insert image description here

This way, everything is utf8, which basically solves the problem.

5. Solution Effect

Test to verify whether the problem is really solved:

insert image description here

insert image description here

This concludes this article about MySQL Basics - How to Solve Errors When Storing Chinese Characters into the Database. For more information on how to solve errors when storing Chinese characters into the database, please search for previous articles on 123WORDPRESS.COM or continue browsing the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Solve the problem of mysql data loss when docker restarts redis
  • Solution to the problem of data loss when using Replace operation in MySQL
  • Several solutions to prevent MySQL data loss when the server goes down
  • Why the disk space is not released after deleting data in MySQL
  • Python Basics: Operating MySQL Database
  • Django saves pictures to MySQL database and displays them on the front-end page
  • MyBatis batch insert/modify/delete MySql data
  • Implementation code for saving images to MySQL database and displaying them on the front-end page
  • Summary of MySQL data migration
  • Golang implements the submission and rollback of MySQL database transactions
  • Detailed explanation of the role of the default database after MySQL installation
  • Causes and solutions for MySQL data loss

<<:  Teach you how to quickly enable self-monitoring of Apache SkyWalking

>>:  Learn v-model and its modifiers in one article

Recommend

Installation and use tutorial of Elasticsearch tool cerebro

Cerebro is an evolution of the Elasticsearch Kopf...

HTML+jQuery to implement a simple login page

Table of contents Introduction Public code (backe...

How to create a my.ini file in the MySQL 5.7.19 installation directory

In the previous article, I introduced the detaile...

Use of LRU algorithm in Vue built-in component keep-alive

Table of contents The use of Vue's keep-alive...

View disk IO in Linux and find out the processes that occupy high IO read and write

Background - Online Alert An online server issued...

Vue uses Baidu Maps to realize city positioning

This article shares the specific code of Vue usin...

The most complete package.json analysis

Table of contents 1. Overview 2. Name field 3. Ve...

Example of javascript bubble sort

Table of contents 1. What is Bubble Sort 2. Give ...

Two ways to reset the root password of MySQL database using lnmp

The first method: Use Junge's one-click scrip...

Based on JavaScript ES new features let and const keywords

Table of contents 1. let keyword 1.1 Basic Usage ...

Summary of CSS sibling element floating analysis

float:left/right/none; 1. Same level floating (1)...

Detailed example of using js fetch asynchronous request

Table of contents Understanding Asynchrony fetch(...

MySQL latest version 8.0.17 decompression version installation tutorial

Personally, I think the decompressed version is e...

Vue implements user login switching

This article example shares the specific code of ...

Use Docker to build a Git image using the clone repository

Overview I have been using Docker for more than a...