MySQL 5.6 使用 unicode 字符集创建视图 [英] MySQL 5.6 create view with unicode character set

问题描述

MySQL 5.6.我无法在视图中获取字符串常量以针对具有默认 UCS2 字符集的数据库正确填充.在 5.7 上运行良好.

我在下面创建了一个可重现的示例.

DROP SCHEMA IF EXISTS test3;CREATE SCHEMA test3 CHARACTER SET ucs2;连接测试3；创建表测试表(测试名 VARCHAR(15));INSERT INTO testTable( testname ) VALUES ('foo');INSERT INTO testTable( testname ) VALUES ('bar');创建或替换视图 testview ASSELECT * FROM 测试表WHERE 测试名 = 'foo';选择 * 从测试视图；

^^^ 这个选择语句没有返回结果.

<前>MySQL [test3]> 显示创建视图 testview \G**************************** 1. 行 ***************************视图:测试视图创建视图:CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`SQL 安全定义器视图`testview` AS 选择`testtable`.`testname` AS`testname` 来自 `testtable` where (`testtable`.`testname` = '\0\0\0f\0\0\0o\0\0\0o')字符集客户端:utf8collat​​ion_connection: utf8_general_ci

那是什么，utf32??

以下确实有效，但我不想将排序规则直接写入语句中，因为这需要是可移植的代码并且语法看起来不标准:

CREATE OR REPLACE VIEW testview ASSELECT * FROM 测试表WHERE testname = 'foo' COLLATE utf8_general_ci;

我尝试将客户端、连接和服务器字符集设置为 ucs2 和 utf16，但这没有任何改变.与 *_general_ci 的排序规则类似.

有任何想法吗?

MySQL [test3]> 显示变量如char%"；+-------------------------+-----------------------------------------------------+|变量名 |价值 |+-------------------------+-----------------------------------------------------+|character_set_client |utf8 ||字符集连接|utf8 ||字符集数据库|ucs2 ||character_set_filesystem |二进制 ||character_set_results |utf8 ||character_set_server |拉丁语1 ||字符集系统|utf8 ||character_sets_dir |C:\Program Files\MySQL\mysql-5.6.36-winx64\share\charsets\ |+-------------------------+-----------------------------------------------------+

解决方案

基本上没有理由在 MySQL 表中使用 usc2、utf16 或 utf32.仅使用 utf8mb4.(如果您使用的是旧版本的 MySQL，则使用 utf8.)

请提供 SHOW VARIABLES LIKE "char%"; 某些内容不应更改:

mysql>显示变量如 "char%";+-------------------------+--------------------+|变量名 |价值 |+-------------------------+--------------------+|character_set_client |utf8mb4 ||字符集连接|utf8mb4 ||字符集数据库|utf8mb4 ||character_set_filesystem |二进制 |<--|character_set_results |utf8mb4 ||character_set_server |utf8mb4 ||字符集系统|utf8 |<--|character_sets_dir |/usr/share/mysql/charsets/|+-------------------------+--------------------+

创建视图时，您没有设置字符集.我可以从你的 SHOW 看到它说:

character_set_client: utf8

MySQL 5.6. I can't get a string constant within a view to populate correctly against a database with default UCS2 character set. Works fine on 5.7.

I've created a minimally reproducible example, below.

DROP SCHEMA IF EXISTS test3;
CREATE SCHEMA test3 CHARACTER SET ucs2;
CONNECT test3;

CREATE TABLE testtable (
testname VARCHAR(15)
);

INSERT INTO testTable( testname ) VALUES ('foo');
INSERT INTO testTable( testname ) VALUES ('bar');

CREATE OR REPLACE VIEW testview AS
SELECT * FROM testtable
WHERE testname = 'foo';

SELECT * FROM testview;

^^^ This select statement returns no results.

MySQL [test3]> show create view testview \G
*************************** 1. row ***************************
                View: testview
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `testview` AS select `testtable`.`testname` AS 
`testname` from `testtable` where (`testtable`.`testname` = '\0\0\0f\0\0\0o\0\0\0o')
character_set_client: utf8
collation_connection: utf8_general_ci

What is that, utf32??

The following does work, but I don't want to write the collation directly into the statement, as this needs to be portable code and the syntax looks non-standard:

CREATE OR REPLACE VIEW testview AS
SELECT * FROM testtable
WHERE testname = 'foo' COLLATE utf8_general_ci;

I have tried setting the client, connection, and server character sets to ucs2 and utf16 but this changed nothing. Likewise with the collations to *_general_ci.

Any ideas?

Edit:

MySQL [test3]> show variables like "char%";
+--------------------------+------------------------------------------------------------+
| Variable_name            | Value                                                      |
+--------------------------+------------------------------------------------------------+
| character_set_client     | utf8                                                       |
| character_set_connection | utf8                                                       |
| character_set_database   | ucs2                                                       |
| character_set_filesystem | binary                                                     |
| character_set_results    | utf8                                                       |
| character_set_server     | latin1                                                     |
| character_set_system     | utf8                                                       |
| character_sets_dir       | C:\Program Files\MySQL\mysql-5.6.36-winx64\share\charsets\ |
+--------------------------+------------------------------------------------------------+

解决方案

There is essentially no reason to ever use usc2 or utf16 or utf32 in MySQL tables. Use utf8mb4 only. (Or utf8 if you have an old version of MySQL.)

Please provide SHOW VARIABLES LIKE "char%"; Certain things should not be changed:

mysql> SHOW VARIABLES LIKE "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     | <--
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       | <--
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

When you created the view, you did not set the charset. I can see that from your SHOW when it said:

character_set_client: utf8

这篇关于MySQL 5.6 使用 unicode 字符集创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！