将CSV拆分至3个MySQL表时遇1054列不存在错误的求助

错误根源分析 #

1. CSV列顺序与LOAD DATA字段列表不匹配：你的CSV前两列是name、class，但LOAD DATA语句里的字段列表第一个写的是student_id，完全错位，导致MySQL找不到math_score_ca这类字段。
2. 未初始化变量@last_student_id：脚本直接使用该变量但未提前赋值，会引发逻辑错误。
3. 后续插入逻辑无效：subjects表本身没有econ_score_ca这类字段，你试图从该表查询这些值插入新记录，根本找不到对应列。

修正后的完整脚本 #

SET FOREIGN_KEY_CHECKS = 0;

-- 重建表（如果需要）
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS results;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  class VARCHAR(10) NOT NULL
);

CREATE TABLE IF NOT EXISTS subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  subject_name VARCHAR(50) NOT NULL,
  subject_ca_score INT DEFAULT NULL,
  subject_exam_score INT DEFAULT NULL,
  subject_score INT DEFAULT NULL,
  subject_type VARCHAR(10) DEFAULT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE IF NOT EXISTS results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  subject_name VARCHAR(50) NOT NULL,
  subject_type VARCHAR(10) NOT NULL,
  subject_ca_score INT NOT NULL,
  subject_exam_score INT NOT NULL,
  subject_score INT NOT NULL,
  grade VARCHAR(2) DEFAULT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 第一步：导入学生基础信息到students表
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(name, class);

-- 第二步：通过临时变量读取CSV中的分数，插入到subjects和results表
-- 处理每个学生的各科数据
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@name, @class, @math_ca, @math_exam, @econ_ca, @econ_exam, @civic_ca, @civic_exam, @lit_ca, @lit_exam)
SET @student_id = (SELECT id FROM students WHERE name = @name AND class = @class)
-- 插入数学科目记录
DO (INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
VALUES (@student_id, 'Math', @math_ca, @math_exam, @math_ca + @math_exam, 'CA'));

DO (INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
VALUES (@student_id, 'Econ', @econ_ca, @econ_exam, @econ_ca + @econ_exam, 'CA'));

DO (INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
VALUES (@student_id, 'Civic', @civic_ca, @civic_exam, @civic_ca + @civic_exam, 'CA'));

DO (INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
VALUES (@student_id, 'Lit', @lit_ca, @lit_exam, @lit_ca + @lit_exam, 'CA'));

-- 插入成绩到results表
DO (INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT @student_id, 'Math', 'CA', @math_ca, @math_exam, @math_ca + @math_exam,
  CASE
    WHEN @math_ca + @math_exam >= 90 THEN 'A'
    WHEN @math_ca + @math_exam >= 80 THEN 'B'
    WHEN @math_ca + @math_exam >= 70 THEN 'C'
    WHEN @math_ca + @math_exam >= 60 THEN 'D'
    ELSE 'F'
  END);

DO (INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT @student_id, 'Econ', 'CA', @econ_ca, @econ_exam, @econ_ca + @econ_exam,
  CASE
    WHEN @econ_ca + @econ_exam >= 90 THEN 'A'
    WHEN @econ_ca + @econ_exam >= 80 THEN 'B'
    WHEN @econ_ca + @econ_exam >= 70 THEN 'C'
    WHEN @econ_ca + @econ_exam >= 60 THEN 'D'
    ELSE 'F'
  END);

DO (INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT @student_id, 'Civic', 'CA', @civic_ca, @civic_exam, @civic_ca + @civic_exam,
  CASE
    WHEN @civic_ca + @civic_exam >= 90 THEN 'A'
    WHEN @civic_ca + @civic_exam >= 80 THEN 'B'
    WHEN @civic_ca + @civic_exam >= 70 THEN 'C'
    WHEN @civic_ca + @civic_exam >= 60 THEN 'D'
    ELSE 'F'
  END);

DO (INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT @student_id, 'Lit', 'CA', @lit_ca, @lit_exam, @lit_ca + @lit_exam,
  CASE
    WHEN @lit_ca + @lit_exam >= 90 THEN 'A'
    WHEN @lit_ca + @lit_exam >= 80 THEN 'B'
    WHEN @lit_ca + @lit_exam >= 70 THEN 'C'
    WHEN @lit_ca + @lit_exam >= 60 THEN 'D'
    ELSE 'F'
  END);

-- 查询结果验证
SELECT s.name, r.subject_name, r.grade
FROM students s
INNER JOIN results r ON s.id = r.student_id;

SET FOREIGN_KEY_CHECKS = 1;

关键修正点 #

• 先把CSV中的学生基础信息导入students表，获取自增的id作为student_id。
• 使用用户变量@name、@math_ca等读取CSV的每一列数据，避免列顺序错位。
• 直接通过变量插入各科记录到subjects和results，不再依赖不存在的表字段。
• 移除无效的@last_student_id变量，改用从students表查询对应id的方式。

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