SQL优化：将movie_rating数据处理后复制到vote_distribution表

核心问题在于你之前用的多子查询会导致数据库对movie_rating表进行多次重复扫描（每行10次子查询的话就是10倍扫描量），效率自然极低。正确的做法是单表扫描+直接字符串拆分计算，一次性完成所有字段的转换和插入。

以下是主流数据库的具体实现代码：

MySQL #

利用SUBSTRING直接截取info字符串的对应位置字符，转换为数值后插入：

INSERT INTO vote_distribution (id, mark1, mark2, mark3, mark4, mark5, mark6, mark7, mark8, mark9, mark10)
SELECT 
    mr.id,
    CAST(SUBSTRING(mr.info, 1, 1) AS UNSIGNED) AS mark1,
    CAST(SUBSTRING(mr.info, 2, 1) AS UNSIGNED) AS mark2,
    CAST(SUBSTRING(mr.info, 3, 1) AS UNSIGNED) AS mark3,
    CAST(SUBSTRING(mr.info, 4, 1) AS UNSIGNED) AS mark4,
    CAST(SUBSTRING(mr.info, 5, 1) AS UNSIGNED) AS mark5,
    CAST(SUBSTRING(mr.info, 6, 1) AS UNSIGNED) AS mark6,
    CAST(SUBSTRING(mr.info, 7, 1) AS UNSIGNED) AS mark7,
    CAST(SUBSTRING(mr.info, 8, 1) AS UNSIGNED) AS mark8,
    CAST(SUBSTRING(mr.info, 9, 1) AS UNSIGNED) AS mark9,
    CAST(SUBSTRING(mr.info, 10, 1) AS UNSIGNED) AS mark10
FROM movie_rating mr;

如果info中存在非数字字符，可通过NULLIF处理为NULL，例如：CAST(NULLIF(SUBSTRING(mr.info,1,1), ' ') AS UNSIGNED)

PostgreSQL #

可以用STRING_TO_ARRAY将字符串拆分为数组后取对应元素，或者直接用SUBSTRING：

INSERT INTO vote_distribution (id, mark1, mark2, mark3, mark4, mark5, mark6, mark7, mark8, mark9, mark10)
SELECT 
    mr.id,
    (STRING_TO_ARRAY(mr.info, ''))[1]::INT AS mark1,
    (STRING_TO_ARRAY(mr.info, ''))[2]::INT AS mark2,
    (STRING_TO_ARRAY(mr.info, ''))[3]::INT AS mark3,
    (STRING_TO_ARRAY(mr.info, ''))[4]::INT AS mark4,
    (STRING_TO_ARRAY(mr.info, ''))[5]::INT AS mark5,
    (STRING_TO_ARRAY(mr.info, ''))[6]::INT AS mark6,
    (STRING_TO_ARRAY(mr.info, ''))[7]::INT AS mark7,
    (STRING_TO_ARRAY(mr.info, ''))[8]::INT AS mark8,
    (STRING_TO_ARRAY(mr.info, ''))[9]::INT AS mark9,
    (STRING_TO_ARRAY(mr.info, ''))[10]::INT AS mark10
FROM movie_rating mr;

SQL Server #

使用SUBSTRING截取并转换：

INSERT INTO vote_distribution (id, mark1, mark2, mark3, mark4, mark5, mark6, mark7, mark8, mark9, mark10)
SELECT 
    mr.id,
    CAST(SUBSTRING(mr.info, 1, 1) AS INT) AS mark1,
    CAST(SUBSTRING(mr.info, 2, 1) AS INT) AS mark2,
    CAST(SUBSTRING(mr.info, 3, 1) AS INT) AS mark3,
    CAST(SUBSTRING(mr.info, 4, 1) AS INT) AS mark4,
    CAST(SUBSTRING(mr.info, 5, 1) AS INT) AS mark5,
    CAST(SUBSTRING(mr.info, 6, 1) AS INT) AS mark6,
    CAST(SUBSTRING(mr.info, 7, 1) AS INT) AS mark7,
    CAST(SUBSTRING(mr.info, 8, 1) AS INT) AS mark8,
    CAST(SUBSTRING(mr.info, 9, 1) AS INT) AS mark9,
    CAST(SUBSTRING(mr.info, 10, 1) AS INT) AS mark10
FROM movie_rating mr;

额外优化建议 #

• 若movie_rating数据量极大，可分批次插入（比如MySQL加LIMIT 10000循环，SQL Server用OFFSET ... FETCH NEXT ...），避免一次性占用过多内存和IO资源。
• 临时关闭vote_distribution的触发器、外键约束（如果有），插入完成后再重新开启，减少插入时的额外校验开销。
• 若vote_distribution有非必要的索引，可先删除索引，插入完成后重建，大幅提升插入速度。

内容的提问来源于stack exchange，提问作者Life knife