使用Union/Union All插入无法插入所有行

在使用 UNION 或 UNION ALL 进行数据插入时，如果发现无法插入所有行，可能是由于以下几个原因导致的：

基础概念

UNION：用于合并两个或多个 SELECT 语句的结果集，并自动去除重复的行。
UNION ALL：与 UNION 类似，但不会去除重复的行，它会返回所有行。

可能的原因及解决方法

1. 数据类型不匹配

确保所有 SELECT 语句中的列具有相同的数据类型。

-- 错误示例
SELECT id, name FROM table1
UNION
SELECT id, age FROM table2; -- name 和 age 数据类型不匹配

-- 正确示例
SELECT id, CAST(name AS VARCHAR(50)) AS name FROM table1
UNION
SELECT id, CAST(age AS VARCHAR(50)) AS name FROM table2;

2. 列数不一致

确保每个 SELECT 语句选择的列数相同。

-- 错误示例
SELECT id, name FROM table1
UNION
SELECT id FROM table2; -- 列数不一致

-- 正确示例
SELECT id, name FROM table1
UNION
SELECT id, '' AS name FROM table2;

3. 空值处理

UNION 默认会对结果进行去重，如果两行数据除了某些列为空外其他都相同，可能会导致某些行被过滤掉。

-- 错误示例
SELECT id, name FROM table1
UNION
SELECT id, NULL AS name FROM table2; -- 可能会丢失某些行

-- 正确示例
SELECT id, name FROM table1
UNION ALL
SELECT id, NULL AS name FROM table2;

4. 索引和约束

如果目标表存在唯一索引或外键约束，可能会因为违反这些约束而无法插入数据。

-- 检查并处理约束
ALTER TABLE target_table DISABLE TRIGGER ALL; -- 禁用触发器
-- 执行插入操作
ALTER TABLE target_table ENABLE TRIGGER ALL; -- 启用触发器

5. 权限问题

确保执行插入操作的用户具有足够的权限。

-- 授予权限
GRANT INSERT ON target_table TO user_name;

应用场景

数据整合：将多个表的数据合并到一个表中。
数据清洗：去除重复数据或进行数据格式统一。
数据迁移：在不同数据库或表之间转移数据。

示例代码

以下是一个完整的示例，展示了如何正确使用 UNION ALL 插入数据：

-- 创建目标表
CREATE TABLE target_table (
    id INT,
    value VARCHAR(100)
);

-- 使用 UNION ALL 插入数据
INSERT INTO target_table (id, value)
SELECT id, name FROM source_table1
UNION ALL
SELECT id, description FROM source_table2;

通过以上步骤和示例代码，可以有效地解决使用 UNION 或 UNION ALL 插入数据时遇到的问题。