Javascript 无法在 MySQL 中插入汉字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14456313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't insert Chinese character into MySQL
提问by user1902899
The cookie is encoded using the big5 set, and it cannot insert into MySQL. Could you help me to solve this problem?
cookie 使用 big5 集编码，无法插入 MySQL。你能帮我解决这个问题吗？
Fields: usernameis eng, date1is date, reason1is a Chinese character.
字段：username是eng，date1是日期，reason1是汉字。
$reason1 = $_COOKIE["reason"];
$sql2="INSERT INTO
attendance_count(username,date,count_time,appendix)
VALUES ('$username','$date1','0','$reason1')";
mysql_query($sql2);
回答by Dipesh Parmar
Use UTF-8when you create table.
创建表时使用UTF-8。
create table table_name () CHARACTER SET = utf8;Use UTF-8when you insert to table
插入表时使用UTF-8
set username utf8; INSERT INTO table_name (ABC,VAL);Detailed Code
详细代码
Below code is tested code please do the following its works.
下面的代码是经过测试的代码，请执行以下操作。
If you have already created database please ALTER it as below code. And if you didn't created database then create it and set Collation to utf8_unicode_ci
如果您已经创建了数据库，请将其更改为以下代码。如果您没有创建数据库，则创建它并将 Collation 设置为 utf8_unicode_ci
And same for table define Collation as "utf8_unicode_ci" for table fields in which you want to store chines chars.
对于要在其中存储中文字符的表字段，表定义排序规则为“utf8_unicode_ci”。
ALTER DATABASE `stackoverflow` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE `stackoverflow`.`chines`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`words` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE = InnoDB;<?php
define('HOSTNAME', 'localhost');
define('USERNAME', 'root');
define('PASSWORD', '');
define('DATABASE', 'stackoverflow');
$dbLink = mysql_connect(HOSTNAME, USERNAME, PASSWORD)or die(mysql_error());
mysql_query("SET character_set_results=utf8", $dbLink)or die(mysql_error());
mb_language('uni');
mb_internal_encoding('UTF-8');
mysql_select_db(DATABASE, $dbLink)or die(mysql_error());
mysql_query("set names 'utf8'",$dbLink)or die(mysql_error());
if(isset($_POST['addWord']))
{
mysql_query("SET character_set_client=utf8", $dbLink)or die(mysql_error());
mysql_query("SET character_set_connection=utf8", $dbLink)or die(mysql_error());
$sql_query = "INSERT INTO chines(words) VALUES('".$_POST['words']."')";
mysql_query($sql_query, $dbLink)or die(mysql_error());
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<title></title>
</head>
<body>
<?php
mysql_query("SET character_set_results=utf8", $dbLink);
$sql_query = "SELECT * FROM chines";
$dbResult = mysql_query( $sql_query, $dbLink)or die(mysql_error());
?>
<form action="" method="post">
<p>Word : <input type="text" name="words" /></p>
<p><input type="submit" name="addWord" /></p>
</form>
<?php
while($row = mysql_fetch_assoc($dbResult))
{
echo $row['words']. '<br />';
}
?>
</body>
</html>See the result and step visual as below.
查看结果和步骤视觉如下。
回答by Rick James
For Chinese, you really need utf8mb4, not utf8. This is because of a small number of Chinese characters that require 4-byte UTF8 characters, which MySQL's utf8does not support.
对于china人，你真的需要utf8mb4，而不是utf8。这是因为有少量中文字符需要 4 字节的 UTF8 字符，而 MySQLutf8不支持。
回答by Rick James
The real problem is about the cookie being encoded big5? Is the rest of the data in the clientencoded as utf8? If 'yes' to both, ...
真正的问题是关于 cookie 被编码big5？客户端中的其余数据是否编码为utf8？如果两者都为“是”，...
Change the connection to be big5, do the INSERT/SELECTof the cookie, then change back to utf8 (or, better, utf8mb4 after upgrading).
将连接更改为big5，执行cookie的INSERT/ SELECT，然后更改回 utf8（或者，升级后最好是 utf8mb4）。
Executing SET NAMES big5or calling the charset('big5')function for the API you are using is the preferred way to switch back and forth.
为您正在使用的 API执行SET NAMES big5或调用charset('big5')函数是来回切换的首选方式。
(Meanwhile, get away from the mysql_*interface; switch to mysqli_*or PDO.)
（同时，离开mysql_*界面；切换到mysqli_*或PDO。）
If everything in the client is big5, then there is no need to switch back and forth. Simply declare that the client is using big5. Note: It does not matter whether the table/column is also big5; MySQL will convert between them.
如果客户端中的一切都是big5，则无需来回切换。只需声明客户端正在使用 big5。注意：表/列是否也是big5都没有关系；MySQL 将在它们之间进行转换。
回答by phvish
update character set of table columns as well with utf8 along with table character set. May be check in Mysql workbench alter table and see character set of columns you want to support Chinese characters.
更新表列的字符集以及 utf8 和表字符集。可以检查Mysql workbench alter table并查看您要支持中文字符的列的字符集。
