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

Solve the problem that the repository and tag names are both none after Docker loads a new image

You can use the command: docker tag [image id] [n...

iframe adaptive size implementation code

Page domain relationship: The main page a.html bel...

Implementation of select multiple data loading optimization in Element

Table of contents Scenario Code Implementation Su...

MySQL query method with multiple conditions

mysql query with multiple conditions Environment:...

Detailed tutorial for downloading and installing mysql8.0.21

Official website address: https://www.mysql.com/ ...

Detailed explanation of JavaScript error capture

Table of contents 1. Basic usage and logic 2. Fea...

Install Mininet from source code on Ubuntu 16.04

Mininet Mininet is a lightweight software defined...

Explanation on whether to choose paging or loading in interactive design

The author of this article @子木yoyo posted it on hi...

Detailed explanation of JavaScript to monitor route changes

Table of contents history pushState() Method push...

LayUI+Shiro implements a dynamic menu and remembers the example of menu expansion

Table of contents 1. Maven Dependency 2. Menu rel...

MySQL encoding utf8 and utf8mb4 utf8mb4_unicode_ci and utf8mb4_general_ci

Reference: MySQL character set summary utf8mb4 ha...

How to import CSS styles into HTML external style sheets

The link-in style is to put all the styles in one...

Solution to the error message "java.sql.SQLException: Incorrect string value:'\xF0\x9F\x92\xA9\x0D\x0A...'" when storing emoticons in MySQL

Preface This article mainly introduces the soluti...

Tutorial on downloading, installing and deploying Tomcat to IDEA (with two hot deployment setting methods for IDEA)

Table of contents Tomcat Download Tutorial Tomcat...

Detailed steps to change the default password when installing MySQL in Ubuntu

Step 1: Enter the directory: cd /etc/mysql, view ...