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

Design Theory: Text Legibility and Readability

<br />Not long ago, due to business needs, I...

About deploying a web project to Alibaba Cloud Server (5 steps to do it)

1. First log in to the Alibaba Cloud website to r...

8 tips for Vue that you will learn after reading it

1. Always use :key in v-for Using the key attribu...

Summary of knowledge points about covering index in MySQL

If an index contains (or covers) the values ​​of ...

Detailed use of Echarts in vue2 vue3

Table of contents 1. Installation 2. Use Echarts ...

Q&A: Differences between XML and HTML

Q: I don’t know what is the difference between xml...

Detailed explanation of the deep and shallow cloning principles of JavaScript arrays and non-array objects

Table of contents What are shallow cloning and de...

The whole process of Vue page first load optimization

Table of contents Preface 1. Image Optimization 2...

Parse CSS to extract image theme color function (tips)

background It all started when a classmate in the...

Solve the error of starting MongoDB: error while loading shared libraries: libstdc++.so.6:cannot open shared object file:

When starting MongoDB, the prompt is: error while...

Introduction to Computed Properties in Vue

Table of contents 1. What is a calculated propert...

Complete steps for Nginx to configure anti-hotlinking

need: Usually, sites want to prevent videos and p...

Node.js adds, deletes, modifies and checks the actual combat record of MySQL database

Table of contents Three steps to operate the data...

How to decompress multiple files using the unzip command in Linux

Solution to the problem that there is no unzip co...

Solve the black screen problem after VMware installs Linux system and starts

1. Installation environment 1. HUAWEI mate x cpu ...