说明 CREATE TABLE SELECT 默认忽略隐藏列时如何显式选择并检查新表属性

在批量归档或临时表构建中，CREATE TABLE ... SELECT（CTAS）很容易让隐藏列的行为被忽略：源表里存在 INVISIBLE 列，复制后的新表却找不到它；把列写进 SELECT 后，列又出现了，但可见性变了。关键规则是：CTAS 的 SELECT 部分决定哪些列进入结果集，而新表列定义决定这些列在目标表中的属性。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/invisible-columns.html

如果只写 SELECT *，源表的 INVISIBLE 列不会进入 CTAS 结果；如果在 SELECT 中显式写出它，列会进入新表，但默认会变成可见列。要保留隐藏属性，需要在 CREATE TABLE 的列定义中再次写出 INVISIBLE，最后用建表语句和元数据查询确认。

• SELECT *：不包含源表的隐藏列。
• 显式列清单：可以把隐藏列带入 CTAS 结果。
• 目标列定义：决定新表是否继续隐藏该列。
• 结构检查：优先看 SHOW CREATE TABLE，再用 INFORMATION_SCHEMA.COLUMNS.EXTRA 交叉确认。

批量复制场景里，真正的分界线在哪里

假设订单表为了兼容旧应用，增加了一个不参与 SELECT * 的追踪列 trace_id。夜间任务需要通过 CTAS 生成归档表。旧脚本通常只关心“数据是否复制”，但在规模化归档中，列集合和列属性同样是契约：漏掉追踪列会让后续审计链断开，错误地公开隐藏列又可能改变下游的通配查询。

这里要把两个问题拆开判断：

1. 源列有没有进入 CTAS 的查询结果？这是 SELECT 投影负责的事。
2. 进入新表后是否仍然隐藏？这是 CREATE TABLE 的列定义负责的事。

把这两层混成“复制表结构”会产生误判。CTAS 更像是先生成一个查询结果，再根据结果列创建目标表，并不是无条件复制源表的完整列属性。

只写 SELECT * 时，为什么隐藏列会消失

MySQL 的隐藏列不会参与 * 展开，因此下面的 CTAS 只会把可见列带到新表。示例中的注释说明了每条语句关注的边界，执行时请把表名替换为实际对象。

-- 创建带有一个隐藏追踪列的源表；旧应用仍可安全使用 SELECT *。
CREATE TABLE orders_live (
  order_id BIGINT NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  trace_id CHAR(32) INVISIBLE
);

-- SELECT * 不展开 trace_id，所以 CTAS 的结果列只包含可见列。
CREATE TABLE orders_archive_default AS
SELECT *
FROM orders_live;

此时不要只根据“表创建成功”判断复制完整。orders_archive_default 能正常生成，并不代表源表的隐藏列也被带过去；CTAS 的列来源是查询结果，而不是源表的全部列定义。

显式选择为什么会改变新表可见性

如果归档表确实需要保存 trace_id，可以在 SELECT 中明确列出它。这样列会进入新表，但源表的 INVISIBLE 属性不会自动沿用。下面两条语句表达的是“带数据列进去”和“保留目标属性”两个不同动作。

-- 显式引用隐藏列，让它进入 CTAS 的查询结果。
CREATE TABLE orders_archive_visible AS
SELECT order_id, amount, trace_id
FROM orders_live;

-- 这里的 trace_id 会成为新表中的普通可见列，不能把它当作仍然隐藏。
-- 需要隐藏时，必须在 CREATE TABLE 部分重新声明属性。

这正是批量任务中最容易遗漏的取舍：显式列清单解决了“列缺失”，却没有解决“属性保持”。如果新表会被旧报表继续使用 SELECT *，那么追踪列从隐藏变为可见，可能让下游列数、导出字段或程序映射发生变化。

需要保持隐藏时，把属性写回 CREATE TABLE

要让列既进入新表又继续隐藏，应在 CTAS 的 CREATE TABLE 部分写出目标列定义，并在 AS SELECT 中显式提供同名列。列定义中的 INVISIBLE 是目标表的契约，不是对源列属性的自动继承。

-- 显式定义目标列的可见性，再从源表取出对应数据。
CREATE TABLE orders_archive_hidden (
  order_id BIGINT NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  trace_id CHAR(32) INVISIBLE
)
AS
SELECT order_id, amount, trace_id
FROM orders_live;

实际迁移中还要注意：CTAS 的列定义主要用于约束列名、类型或可见性，其他默认值、索引、外键等结构细节不能想当然地视为完整复制。若归档表需要完整的索引和约束，应把“结构创建”和“数据装载”拆成明确的 DDL 与 DML 流程；本文只聚焦隐藏列在 CTAS 中的选择和可见性变化。

用什么方式检查新表属性

在高压批处理里，检查动作应当短而确定：先看完整建表语句，确认是否出现 INVISIBLE；再查数据字典的 EXTRA 字段，确认具体列的元数据。不要把“查询能返回数据”当成“列属性正确”。

-- SHOW CREATE TABLE 适合确认目标表的完整列定义与 INVisible 标记。
SHOW CREATE TABLE orders_archive_hidden;

-- INFORMATION_SCHEMA.COLUMNS.EXTRA 会把隐藏列标记为 INVISIBLE。
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('orders_archive_default', 'orders_archive_visible', 'orders_archive_hidden')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 用显式列清单检查数据查询，不让 SELECT * 的默认行为掩盖列是否存在。
SELECT order_id, amount, trace_id
FROM orders_archive_hidden;

检查结果可以按三种状态理解：默认 CTAS 中找不到 trace_id，说明它没有进入 SELECT *；显式选择的 CTAS 中有 trace_id 但 EXTRA 为空，说明它进入了新表并变为可见；显式选择且目标列声明 INVISIBLE 时，EXTRA 应显示 INVISIBLE。

上线前的取舍：数据完整和兼容性不能只选一个

如果归档或临时表只供新程序使用，显式选择隐藏列并让它变为可见，通常更直观；如果新表还会被旧程序用 SELECT * 读取，就应在目标 DDL 中保持 INVISIBLE，避免列集合悄然扩大。两种方案都能保存数据，差别在于是否改变通配查询的外部行为。

建议把下面三项写进批处理的结构约定：

• 列清单：必须显式声明哪些隐藏列需要复制。
• 可见性：明确新表是公开列还是继续隐藏，不能依赖默认值。
• 验收信号：记录 SHOW CREATE TABLE 与 INFORMATION_SCHEMA.COLUMNS.EXTRA 的检查结果。

这样做的成本只是多写一段目标列定义和一次元数据查询，却能把一次性脚本变成可复用的结构契约。后续若新增更多隐藏列，也只需在显式列清单和目标定义中分别做出决定。

常见问题

CTAS 用 SELECT * 能复制 INVISIBLE 列吗？

不能。* 不包含隐藏列；如果需要复制，必须在 SELECT 中显式写出列名。

显式写出隐藏列后，新表也会隐藏吗？

不会自动隐藏。显式选择只负责让列进入查询结果；要保持隐藏，需要在 CREATE TABLE 的目标列定义中写 INVISIBLE。

为什么还要查 INFORMATION_SCHEMA？

SHOW CREATE TABLE 适合看完整 DDL，INFORMATION_SCHEMA.COLUMNS.EXTRA 适合按列确认可见性。两者结合可以减少只看数据结果造成的误判。

本文是否讨论 CREATE TABLE LIKE？

不讨论。本文只覆盖 CREATE TABLE ... SELECT 对隐藏列的选择与可见性变化；需要比较 CREATE TABLE ... LIKE 时应单独按其结构复制规则设计方案。