python mysql Error: (1366, “Incorrect string value:

又遇到新的問題，在  Connection 增加了 charset=”utf8″ 之後，原本的錯誤訊息從

mysql Error: 'latin-1' codec can't encode character u

變成：

Error: (1366, "Incorrect string value:

 

先下指令看看 database schema:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

或指令：

show variables like 'char%';

 

原來 database 被設成 latin1, 所以會出錯，要解法如下：

---

Use the ALTER DATABASE and ALTER TABLE commands.

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Or if you’re still on MySQL 5.5.2 or older which didn’t support 4-byte UTF-8, use utf8 instead of utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

---

原來，先下這2個指令，才能修改 connection charset, 之後建立的  database & table, charset 才會是對的。

mysql>

SET NAMES 'utf8';
SET CHARACTER SET utf8;

---

I would not suggest Richies answer, because you are screwing up the data inside the database. You would not fix your problem but try to “hide” it and not being able to perform essential database operations with the crapped data.

If you encounter this error either the data you are sending is not UTF-8 encoded, or your connection is not UTF-8. First, verify, that the data source (a file, …) really is UTF-8.

Then, check your database connection, you should do this after connecting:

SET NAMES 'utf8';
SET CHARACTER SET utf8;

Next, verify that the tables where the data is stored have the utf8 character set:

SELECT
  `tables`.`TABLE_NAME`,
  `collations`.`character_set_name`
FROM
  `information_schema`.`TABLES` AS `tables`,
  `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations`
WHERE
  `tables`.`table_schema` = DATABASE()
  AND `collations`.`collation_name` = `tables`.`table_collation`
;

Last, check your database settings:

mysql> show variables like '%colla%';
mysql> show variables like '%charac%';

If source, transport and destination are UTF-8, your problem is gone;)

---

change database collation:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change table collation:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change column collation:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

More info:

• What are the diffrences between utf8_general_ci and utf8_unicode_ci?
• What’s the difference between utf8_general_ci and utf8_unicode_ci
• How to change collation of database, table, column?

---

自動修改所有table的 python script:

If you have this problem here’s a python script to change all the columns of your mysql database automatically.

#! /usr/bin/env python
import MySQLdb

host = "localhost"
passwd = "passwd"
user = "youruser"
dbname = "yourdbname"

db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname)
cursor = db.cursor()

cursor.execute("ALTER DATABASE `%s` CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'" % dbname)

sql = "SELECT DISTINCT(table_name) FROM information_schema.columns WHERE table_schema = '%s'" % dbname
cursor.execute(sql)

results = cursor.fetchall()
for row in results:
  sql = "ALTER TABLE `%s` convert to character set DEFAULT COLLATE DEFAULT" % (row[0])
  cursor.execute(sql)
db.close()

 

相關文章：

How to fix “Incorrect string value” errors?
http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors

How to change the default collation of a database?
http://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database