MySQL表定义中如何将CSV的yes/no转换为BOOL类型的true/false

问题背景 #

DBMS课程作业任务：设计并实现关系数据库，使用MySQL定义表结构，导入包含航班与野生动物碰撞数据的CSV文件。其中flights表的isHeavy字段为BOOL类型，需要将CSV中heavy_flag列的yes转为true，no转为false。

原表结构代码（存在语法问题） #

f <- paste0(
  " CREATE TABLE flights(",
    "fid INT NOT NULL",
    "date DATE NOT NULL",
    "originAirport INT NOT NULL",
    "airlineName VARCHAR(255) NOT NULL",
    "aircraftType VARCHAR(255) NOT NULL",
    "isHeavy BOOL NOT NULL,",
    "PRIMARY KEY (fid)",
    "FOREIGN KEY (originAirport) REFERENCES airports(aid)",
    
  ")"
  
)

用户错误尝试 #

用户试图在表结构定义中直接写入数据转换逻辑，这不符合MySQL语法规范：

f <- paste0(
  " CREATE TABLE flights(",
    "fid INT NOT NULL",
    "date DATE NOT NULL",
    "originAirport INT NOT NULL",
    "airlineName VARCHAR(255) NOT NULL",
    "aircraftType VARCHAR(255) NOT NULL",
    "isHeavy BOOL TRUE if \"is_heavy\" = \"yes\" else FALSE,",
    "PRIMARY KEY (fid)",
    "FOREIGN KEY (originAirport) REFERENCES airports(aid)",
    
  ")"
  
)

正确解决方案 #

1. 修正表结构定义 #

表结构仅需指定字段类型与约束，不能在定义中写入数据转换逻辑。同时需补全原代码中缺失的字段分隔逗号：

f <- paste0(
  " CREATE TABLE flights(",
    "fid INT NOT NULL,",
    "date DATE NOT NULL,",
    "originAirport INT NOT NULL,",
    "airlineName VARCHAR(255) NOT NULL,",
    "aircraftType VARCHAR(255) NOT NULL,",
    "isHeavy BOOL NOT NULL,",
    "PRIMARY KEY (fid),",
    "FOREIGN KEY (originAirport) REFERENCES airports(aid)",
  ")"
)

2. 导入CSV时处理isHeavy字段转换 #

数据转换需在导入阶段完成，推荐两种实现方式：

• MySQL原生导入语句：使用LOAD DATA INFILE直接在导入时完成转换

LOAD DATA INFILE '/path/to/your/csv/file.csv'
INTO TABLE flights
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- 若CSV包含表头则忽略第一行
(fid, date, originAirport, airlineName, aircraftType, @heavy_flag)
SET isHeavy = CASE WHEN @heavy_flag = 'yes' THEN TRUE ELSE FALSE END;

• R语言导入处理：先读取CSV转换数据，再写入数据库

# 读取CSV文件
flight_data <- read.csv("/path/to/your/csv/file.csv")
# 转换heavy_flag列为BOOL类型
flight_data$isHeavy <- ifelse(flight_data$heavy_flag == "yes", TRUE, FALSE)
# 写入数据库（假设已建立数据库连接conn）
DBI::dbWriteTable(conn, "flights", flight_data, append = TRUE)

内容的提问来源于stack exchange，提问作者Muzhdah-W