通过表格向数据库添加表情符号时出现不正确的字符串错误 [英] Incorrect String error when adding emoji to a database through a form

问题描述

我可以在终端上使用mysql成功将表情符号(即utf8mb4数据)添加到表中.

I can successfully add emoji (i.e. utf8mb4 data) to tables using mysql using the terminal.

当我的Python Flask网站尝试将表情符号发送到相同的数据库表和字段时，数据库返回以下不正确的字符串错误:

When my Python Flask website tries to send emoji to the same database table and field, the database returns the following incorrect string error:

(1366，第1行的"p_description"列的字符串值不正确:'\ xF0 \ x9F \ x98 \ x8E')

(1366, "Incorrect string value: '\xF0\x9F\x98\x8E' for column 'p_description' at row 1")

更新

• 我阅读了建议的线程，但没有看到如何阻止服务器覆盖我的本地字符集服务器设置.
• 添加了SET NAMES utf8mb4;在INSERT INTO之前，但没有效果
• 我按照注释中的建议进行了检查(谢谢！)，是的，我所有的数据库字符设置都在可能的地方显示utf8mb4.这是我从mysql得到的:

mysql>在变量名喜欢'character_set_％'或变量名喜欢'collat​​ion％'的地方显示变量;

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

=>

|变量名|值|

| character_set_client | utf8mb4 |

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem |二进制|

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_set_system | utf8 |

| collat​​ion_connection | utf8mb4_unicode_ci |

| collation_connection | utf8mb4_unicode_ci |

| collat​​ion_database | utf8mb4_unicode_ci |

| collation_database | utf8mb4_unicode_ci |

| collat​​ion_server | utf8mb4_unicode_ci |

| collation_server | utf8mb4_unicode_ci |

组10行(0.00秒)

10 rows in set (0.00 sec)

我正在使用html表单，jQuery，AJAX和Python Flask将数据发送到数据库. Python调用下面的SQL存储过程.

I am using an html form, jQuery, AJAX and Python Flask to send the data to the database. Python calls the SQL stored procedure below.

存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addWish`(
    IN p_title varchar(45),
    IN p_description varchar(1000),
    IN p_user_id bigint
)
BEGIN
    SET NAMES utf8mb4; insert into tbl_wish(
        wish_title,
        wish_description,
        wish_user_id,
        wish_date
    )
    values
    (
        p_title,
        p_description,
        p_user_id,
        NOW()
    );
END

**问:如何强制我的网站以utf8mb4格式将数据发送到数据库?

**Q: How do I force my website to send data to my database in the utf8mb4 format?

推荐答案

Python Flask默认在MySQL的utf-8中与MySQL通信，即它无法处理完整的utf8mb4范围(包括表情符号). Flask将覆盖数据库字符集设置，包括my.cf中的字符集服务器设置.将以下设置添加到Flask应用程序可通过强制其与utf8mb4中的MySQL通信来解决该问题:

Python Flask defaults to communicating with MySQL in MySQL's utf-8, i.e. it can't handle the full utf8mb4 range (which includes emojis). Flask will override the database charset settings, including the character-set-server setting in my.cf. Adding the following setting to the Flask app fixes the problem by forcing it to communicate with MySQL in utf8mb4:

app.config ['MYSQL_DATABASE_CHARSET'] ='utf8mb4'

app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'

这篇关于通过表格向数据库添加表情符号时出现不正确的字符串错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！