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

Detailed explanation of the difference between "/" and "~" in Linux

"/" is the root directory, and "~&...

CSS3 mouse hover transition zoom effect

The following is a picture mouse hover zoom effec...

5 ways to achieve the diagonal header effect in the table

Everyone must be familiar with table. We often en...

Four modes of Oracle opening and closing

>1 Start the database In the cmd command windo...

Reasons and solutions for not being able to detect array changes in Vue2

Table of contents Workaround Why can't I moni...

Build a high-availability MySQL cluster with dual VIP

Table of contents 1. Project Description: 2. Proj...

TypeScript learning notes: type narrowing

Table of contents Preface Type Inference Truth va...

How to use Docker to limit container resources

Problem Peeping In the server, assuming that the ...

Use href in html to pop up a file download dialog box when clicking a link

I learned a new trick today. I didn’t know it befo...

Installation and verification of pytorch in linux or windows environment (solving runtimeerror problem)

1. Download the corresponding installation file f...

A Brief Analysis of MySQL PHP Syntax

Let's first look at the basic syntax of the c...

Three ways to prevent MySQL from inserting duplicate data

Create a new table CREATE TABLE `person` ( `id` i...

How to reset the root password in CentOS7

There are various environmental and configuration...

Solve the problem of Navicat for MySQL reporting 2005 error when connecting to MySQL

In the previous article, I introduced how to solv...

Detailed explanation of possible problems in converting floating point data to character data in MySQL

Preface This article mainly introduces a problem ...