将单个CSV拆分为多个Mysql表而不抛出错误未知列丢失

Question

我想创建sql脚本，将一个csv分成3个mysql表

然而，无论何时，我运行下面的sql脚本，它抛出一个错误，没有主题或在字段列表中。我已经交叉了我的csv，所有的标题和数据都是完整的。下面是sql脚本:***

脚本的第一部分创建实现此任务所需的所有表。

第二部分使用load data infile 将csv加载到这些表中

-- Create students table
SET FOREIGN_KEY_CHECKS = 0;
-- Create students table
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL
);
-- Create subjects table
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 results table
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)
);
-- Load data into the subjects table from CSV
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(student_id, math_score_ca, math_score_exam, econ_score_ca, econ_score_exam, civic_score_ca, civic_score_exam, lit_score_ca, lit_score_exam)
SET
subject_name = 'Math',
subject_ca_score = math_score_ca,
subject_exam_score = math_score_exam,
subject_score = math_score_ca + math_score_exam,
subject_type = 'CA',
student_id = @last_student_id + 1;
-- Insert records for each subject into the subjects table
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Econ', econ_score_ca, econ_score_exam, econ_score_ca + econ_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Civic', civic_score_ca, civic_score_exam, civic_score_ca + civic_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Lit', lit_score_ca, lit_score_exam, lit_score_ca + lit_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
-- Calculate grades and insert them into the results table
INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT student_id, subject_name, 'CA', subject_ca_score, subject_exam_score, subject_ca_score + subject_exam_score,
CASE
WHEN subject_ca_score + subject_exam_score IS NULL THEN NULL
WHEN subject_ca_score + subject_exam_score >= 90 THEN 'A'
WHEN subject_ca_score + subject_exam_score >= 80 THEN 'B'
WHEN subject_ca_score + subject_exam_score >= 70 THEN 'C'
WHEN subject_ca_score + subject_exam_score >= 60 THEN 'D'
ELSE 'F'
END
FROM subjects
WHERE student_id = @last_student_id +1;
-- Display the student's name and their results
SELECT s.name, r.subject_name, r.grade
FROM students s
INNER JOIN results r ON s.id = r.student_id
WHERE s.id = @last_student_id;
-- End of script.
SET FOREIGN_KEY_CHECKS = 1;

错误:

SET FOREIGN_KEY_CHECKS = 0
> OK
> Query Time: 0.001s


-- Create students table
SET FOREIGN_KEY_CHECKS = 0
> OK
> Query Time: 0.009s


-- Create students table
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL
)
> OK
> Query Time: 0.001s


-- Create subjects table
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)
)
> OK
> Query Time: 0.006s


-- Create results table
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)
)
> OK
> Query Time: 0.002s


-- Load data into the subjects table from CSV
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(student_id, math_score_ca, math_score_exam, econ_score_ca, econ_score_exam, civic_score_ca, civic_score_exam, lit_score_ca, lit_score_exam)
SET
subject_name = 'Math',
subject_ca_score = math_score_ca,
subject_exam_score = math_score_exam,
subject_score = math_score_ca + math_score_exam,
subject_type = 'CA',
student_id = @last_student_id + 1
> 1054 - Unknown column 'math_score_ca' in 'field list'
> Query Time: 0.009s

这是一个csv文件，当用文本编辑器notepad++打开时，

name,class,math_score_ca,math_score_exam,econ_score_ca,econ_score_exam,civic_score_ca,civic_score_exam,lit_score_ca,lit_score_exam
John Doe,SS3,70,80,75,85,80,90,85,95
Jane Doe,SS3,85,90,80,75,90,85,70,80

Answer 1

你的方案不合逻辑。

你创建:

• 单独的Class和SubjectType表;
• 表Student，指Class;
• 表Subject指SubjectType;
• 表Result指Student和Subject。

根据主题区域的细节，方案可能包含更多的表。此外，这些表必须包含合适的唯一约束。

Answer 2

对于您当前使用现有结构的方法(坏主意，请参阅Akina的回答)，您需要对CSV进行多次传递，首先插入学生(不知道您从哪里获得@last_student_id)，然后通过每个主题。

-- ADD UNIQUE KEY TO students (name, class)
ALTER TABLE `students` ADD UNIQUE INDEX (`name`, `class`);
-- LOAD STUDENTS
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv' IGNORE
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@name, @class, @math_score_ca, @math_score_exam, @econ_score_ca, @econ_score_exam, @civic_score_ca, @civic_score_exam, @lit_score_ca, @lit_score_exam)
SET id = NULL, name = @name, class = @class;
-- REPEAT THIS STEP FOR EACH SUBJECT
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@name, @class, @math_score_ca, @math_score_exam, @econ_score_ca, @econ_score_exam, @civic_score_ca, @civic_score_exam, @lit_score_ca, @lit_score_exam)
SET
id = NULL,
student_id = (SELECT id FROM students WHERE name = @name AND class = @class),
subject_name = 'Math',
subject_ca_score = @math_score_ca,
subject_exam_score = @math_score_exam,
subject_score = @math_score_ca + @math_score_exam,
subject_type = 'CA';
-- THEN YOUR INSERT INTO results

一个更好的方法是规范化你的数据结构(这只是一个例子，需要"抛光">以满足你的需求):

CREATE TABLE IF NOT EXISTS classes (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS students (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class_id INT UNSIGNED NOT NULL,
UNIQUE (name, class_id),
FOREIGN KEY (class_id) REFERENCES classes (id)
);
CREATE TABLE IF NOT EXISTS subjects (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
type ENUM('CA' /* ADD OTHER SUBJECT TYPES HERE OR MOVE TO THEIR OWN TABLE */) DEFAULT NULL
);
INSERT INTO subjects VALUES (1, 'Math', 'CA'), (2, 'Econ', 'CA'), (3, 'Civic', 'CA'), (4, 'Lit', 'CA');
CREATE TABLE IF NOT EXISTS results (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
ca_score INT NOT NULL,
exam_score INT NOT NULL,
score INT AS (ca_score + exam_score),
grade VARCHAR(2) AS (CASE WHEN score IS NULL THEN NULL WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' WHEN score >= 60 THEN 'D' ELSE 'F' END),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (subject_id) REFERENCES subjects (id)
);

并将CSV解析为临时表:

CREATE TEMPORARY TABLE import_csv (
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL,
math_score_ca TINYINT UNSIGNED NOT NULL,
math_score_exam TINYINT UNSIGNED NOT NULL,
econ_score_ca TINYINT UNSIGNED NOT NULL,
econ_score_exam TINYINT UNSIGNED NOT NULL,
civic_score_ca TINYINT UNSIGNED NOT NULL,
civic_score_exam TINYINT UNSIGNED NOT NULL,
lit_score_ca TINYINT UNSIGNED NOT NULL,
lit_score_exam TINYINT UNSIGNED NOT NULL
);
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE import_csv
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS;

然后用它来填充你的其他表:

INSERT IGNORE INTO classes (name)
SELECT DISTINCT class
FROM import_csv;
INSERT IGNORE INTO students (name, class_id)
SELECT DISTINCT csv.name, c.id
FROM import_csv csv
JOIN classes c ON c.name = csv.class;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 1 /* Math */, csv.math_score_ca, csv.math_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 2 /* Econ */, csv.econ_score_ca, csv.econ_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 3 /* Civic */, csv.civic_score_ca, csv.civic_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 4 /* Lit */, csv.lit_score_ca, csv.lit_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;