SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xAC15\xE9\x9B\x86...' Solution

$SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: \'\\xAC15\\xE9\\x9B\\x86...\' Solution-1$

In the process of data collection and entry, we often encounterSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value error. This is a problem with the database or program coding.

Many programs use the default utf-8 encoding. Change the utf-8 encoding toutf8mb4That can solve most of the problems.

How to force utf8mb4 in mysql

To force the use of utf8mb4 character set in MySQL, you can configure the character set of the database, table, and connection to utf8mb4 by following these simple steps. The following is the simplest method:

1. Modify the MySQL configuration file

Find the MySQL configuration file (usually my.cnf or my.ini, which may be located in /etc/mysql/my.cnf or /etc/my.cnf, etc., or in the MySQL installation directory under Windows).

Add or modify the following content in the configuration file:

[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect = 'SET NAMES utf8mb4'

After saving, restart the MySQL service:
sudo service mysql restart
Or (under Windows) restart MySQL in the Services Manager.

2. Set the default character set for new databases and tables

Specify utf8mb4 when creating a new database:
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
To change the character set for an existing database:
ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
To change the character set for an existing table:
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Make sure the client connection uses utf8mb4

When connecting to MySQL, set the connection character set:
SET NAMES utf8mb4;
If using a programming language (such as PHP, Python), ensure that the connection configuration specifies utf8mb4:
- PHP (PDO):
  $pdo = new PDO("mysql:host=localhost;dbname=my_database;charset=utf8mb4", "username", "password");
- Python (MySQL Connector):
  import mysql.connector
  conn = mysql.connector.connect(host="localhost", database="my_database", user="username", password="password", charset="utf8mb4")

4. Verify character set settings

Check the global character set:
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
Make sure character_set_server, character_set_database, etc. show utf8mb4 and collation_server is utf8mb4_unicode_ci.

Summary of the simplest method

One-step solution: Modify the my.cnf file, add the above [client], [mysql], and [mysqld] configurations, and restart MySQL. This will enable all newly created databases and tables to use utf8mb4 by default, and client connections will also automatically adapt.
Quickly repair existing tables: Run ALTER statements on existing databases and tables to ensure that all data is stored in utf8mb4.
Notice: If there is data in the database, back up the data before running ALTER TABLE to prevent data loss due to character conversion.

If you encounter a specific issue (such as invalid configuration or garbled data), please provide more details and I can help you troubleshoot further!