在MySQL中从选择中创建表时，我该如何指定排序规则？

Question

创建具有从选择结果动态创建和定义的列的表时，如何指定排序规则，就像在创建带有列定义的表时一样？

例如：

CREATE TABLE IF NOT EXISTS my_table (
  SELECT * FROM (
    SELECT ....
  ) )

以上代码可以成功创建具有动态列的表格，但是它没有使用我想要的排序规则。它使用的是utf8_general_ci，而我想要的是utf8_unicode_ci。

以下代码无效

CREATE TABLE IF NOT EXISTS my_table (
  SELECT * FROM (
    SELECT ....
  ) ) DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

当使用该命令时，出现错误信息提示此位置上的命令无效。

我意识到上述建议是无效的，那么有没有其他方法可以实现呢？我尝试设置数据库排序规则，以为这将为创建的所有表设置默认值，但看起来它并没有像那样工作 - 或者也许当我用ALTER改变数据库排序规则时，实际上我没有设置数据库的默认值。是否有一种方法可以为表设置默认值，使其使用所需的排序规则创建第一个代码块中的表？

上面代码块中的SELECT...结果涉及到各种条件/逻辑值的设置，使用case/when语句、字符串解析和函数结果 - 它不仅是从具有定义列的另一个表逐一选择列，因此不能简单地定义其他表的列。

我已经尝试提前使用ALTER DATABASE my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci设置数据库排序规则，但这并没有改变结果。在从选择结果动态定义的列创建表时，表仍然使用utf8_general_ci（包括表中的列）。

- Streamline

---

CREATE TABLE 的文档概述了几种做这种事情的方法，但在第二种情况下，它们都不符合您的期望。 - tadman

是的，这就是为什么我在这里提出问题的原因。我在上面提供第二个代码块只是建议我想要工作的内容，但我意识到它不正确。我正在寻找正确的解决方案。文档提到可以从选择创建表格以及存在有关排序规则的表格选项，但我找不到同时显示这两个示例的示例。上面的 SELECT.... 是由各种逻辑（如 case when 表达式、其他表和函数结果）创建的结果集合。 - Streamline

Answer 1

以下对于我来说在MariaDB 10版本上有效：

CREATE TABLE my_table ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SELECT *
FROM other_table
;

- giordano

Answer 2

CREATE TABLE my_table (
    ... none, some, or all columns ...
) ... none, some, or all specifications ...  -- Here
    SELECT ...;

如果失败了，那么...

CREATE TABLE my_table ( all columns ) all specs;
INSERT INTO my_table
    SELECT ...;

或者...

CREATE ... SELECT ...;
ALTER TABLE my_table ...;  -- any fixes needed

另一种方法是确保在创建表之前，DATABASE 拥有所需的 DEFAULT CHARACTER SET 和/或 DEFAULT COLLATION。无论你选择哪种方法，请在其后添加以下内容：

SHOW CREATE TABLE my_table;

验证每列是否具有所需的字符集和排序规则。如果在列中缺少，则查看表的默认值。

示例

CREATE TABLE `us` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `country` char(2) CHARACTER SET ascii NOT NULL COMMENT 'ISO 3166 Country Code',
  `ascii_city` varchar(100) CHARACTER SET ascii NOT NULL COMMENT 'Name of city or town in ASCII encoding',
  `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `state` char(2) CHARACTER SET ascii NOT NULL COMMENT 'For US, ISO-3166-2 code for the state/province name.',
  `population` int(10) unsigned NOT NULL DEFAULT '0',
  `lat` float NOT NULL,
  `lng` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `state` (`state`,`city`),
  KEY `state_2` (`state`,`population`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意某些列上的显式CHARACTER SET和COLLATE，从而覆盖表的默认字符集DEFAULT CHARSET。

当没有排序规则时，CHARSET utf8默认为COLLATE utf8_general_ci。

示例2：

在此示例中，我将从该表创建一个名为us2的新表，但更改city的排序规则：

CREATE TABLE us2 (
  `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
)
    SELECT * from us;

关于 us2 的所有内容都与 us 相同，除了该列的排序规则。

- Rick James

---

当你说要确保数据库的默认字符集/排序规则与所需相同，这是否意味着检查变量？例如，如果我在使用相关数据库时，在mysql客户端中检查collation_database系统变量，它会告诉我是utf8_unicode_ci。然而，当像我的帖子中第一个示例那样创建表时，它却被创建为utf8_general_ci。除了使用ALTER DATABASE db_name CHARACTER SET charset COLLATE collation;之外，是否还有其他设置数据库默认值的方法？数据库选项和默认选项是否不同？ - Streamline

根据我上次的评论，我尝试在使用alter database命令时添加单词DEFAULT：ALTER DATABASE my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci，但它仍然不能改变我看到的结果。我仍然得到使用utf8_general_ci创建的表的查询结果。 - Streamline

要检查数据库的默认值，请使用SHOW CREATE DATABASE dbname;。 - Rick James

在不指定排序规则的情况下，使用CHARACTER SET utf8将会给你默认的utf8_general_ci排序规则。 - Rick James

谢谢，但您的示例展示了如何创建一个同时定义列的表。我熟悉如何在这种方式下为表和列定义排序规则。我的情况是需要创建一个表，同时让选择的结果动态地定义新表的列，这就是为什么我不清楚如何强制新表的排序规则。 - Streamline