How to Fix Incorrect String Value in MySQL

Sometimes you may get “Incorrect String Value” error while trying to insert data in MySQL table, or import external data into MySQL database. In this article, we will look at how to fix incorrect string value in MySQL.

How to Fix Incorrect String Value in MySQL

This is a common error when you try to insert value into a MySQL table that does not have a UTF8 encoding or character set.

1. Check Default Character Set

Log into MySQL and run the following command to check the default character set name of your database. Replace database_name with your database name.

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "database_name";

If your database does not have UTF8 encoding, then you need to change it to UTF8. Before that, we need to take a backup of our database.

Also read : Top MySQL Blogs and Websites

2. Backup MySQL database

Log out of MySQL and run the following command to take backup of your MySQL database. Replace user_name, pass_word and database_name with your username, password and database names respectively.

$ sudo mysql -u user_name -p pass_word database_name > backup.sql

Also read : How to Change Column Size in MySQL

3. Change Character Set of Tables

Run the following command to change the character set of all tables in your database to UTF8. Replace database_name with your database name

$ sudo mysql --database=database_name -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=database_name

MySQL allows you to change character set of only one table at a time. It can be tedious to run separate commands to change character set of each table. The above command lists all the tables in a database and converts their character set to UTF8 one by one, automatically.

Also read : How to Store JSON in MySQL

4. Change database character set

Log into MySQL and run the following command to change character set of database to UTF8. Replace database_name with database name.

mysql> ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Now if you to insert data into MySQL database, it should not give errors. Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.