创建含DATE和TIME类型的SQL表时遇无效数据类型问题求助

一、先搞定「无效数据类型」的报错问题 #

你提到要设置fecha为DATE、hora为TIME类型，这个问题大概率和你使用的数据库类型有关——不同数据库对日期时间类型的支持差异不小，我给你分常见情况梳理：

1. 如果你用的是 MySQL/MariaDB #

DATE和TIME是完全合法的类型，先检查建表语句有没有语法小错误，比如漏写逗号、类型名拼写错误之类的。正确的示例应该是这样：

CREATE TABLE tu_tabla (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    -- 其他字段按需求添加
);

如果还是报错，看看是不是数据库版本过于陈旧，但大概率是语法细节没注意到。

2. 如果你用的是 Oracle #

划重点：Oracle 没有单独的TIME类型！它的DATE类型本身就包含日期和时间（精确到秒），如果只需要存储时间部分，你可以用TIMESTAMP类型，或者继续用DATE，插入时只存时间值即可。如果非要分开存日期和时间，日期用DATE，时间可以用INTERVAL DAY TO SECOND（不推荐用VARCHAR2，不利于后续时间运算）。示例建表语句：

CREATE TABLE tu_tabla (
    id NUMBER PRIMARY KEY,
    fecha DATE NOT NULL,
    hora TIMESTAMP NOT NULL,
    -- 其他字段按需求添加
);

要是你在Oracle里写了TIME类型，肯定会报无效数据类型，这是新手常踩的坑！

3. 如果你用的是 PostgreSQL #

PostgreSQL原生支持DATE和TIME类型，建表语法类似MySQL，确认语句格式正确就行：

CREATE TABLE tu_tabla (
    id SERIAL PRIMARY KEY,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    -- 其他字段按需求添加
);

二、从VARCHAR2(50)导入数据并拆分到不同关系 #

既然源数据都是VARCHAR2(50)且集中在一个表里，我们可以分三步来处理：

1. 创建临时表存放源数据 #

先把所有源数据导入到临时表，避免直接操作目标表出错：

-- Oracle示例，MySQL用CREATE TEMPORARY TABLE
CREATE TABLE temp_datos (
    dato_varchar VARCHAR2(50),
    -- 源数据有其他字段的话一起加上
);
-- 用你熟悉的方式导入数据（比如IMPDP、SQL*Loader或者INSERT语句）

2. 把字符串转换为日期/时间类型 #

根据源字符串的具体格式，用数据库对应的转换函数处理：

• Oracle：用TO_DATE转日期，TO_TIMESTAMP转时间，比如假设源日期是YYYY-MM-DD格式、时间是HH24:MI:SS格式：

  SELECT TO_DATE(SUBSTR(dato_varchar, 1, 10), 'YYYY-MM-DD') AS fecha,
         TO_TIMESTAMP(SUBSTR(dato_varchar, 12, 8), 'HH24:MI:SS') AS hora
  FROM temp_datos;
  

• MySQL：用STR_TO_DATE函数：

  SELECT STR_TO_DATE(SUBSTRING(dato_varchar, 1, 10), '%Y-%m-%d') AS fecha,
         STR_TO_DATE(SUBSTRING(dato_varchar, 12, 8), '%H:%i:%s') AS hora
  FROM temp_datos;
  

3. 拆分数据到不同关系表 #

假设你要把数据拆分到clientes和pedidos两个表（换成你的实际表即可），用INSERT INTO...SELECT语句实现，同时关联表之间的关系：

-- 先插入clientes表
INSERT INTO clientes (nombre, fecha_registro)
SELECT SUBSTRING(dato_varchar, 1, 20), -- 假设前20位是客户名
       TO_DATE(SUBSTR(dato_varchar, 21, 10), 'YYYY-MM-DD') -- 21-30位是注册日期
FROM temp_datos
WHERE -- 这里加筛选客户数据的条件

-- 再插入pedidos表，关联clientes表
INSERT INTO pedidos (cliente_id, fecha_pedido, hora_pedido)
SELECT c.id,
       TO_DATE(SUBSTR(td.dato_varchar, 31, 10), 'YYYY-MM-DD'),
       TO_TIMESTAMP(SUBSTR(td.dato_varchar, 42, 8), 'HH24:MI:SS')
FROM temp_datos td
JOIN clientes c ON SUBSTRING(td.dato_varchar, 1, 20) = c.nombre;

核心思路是：从临时表提取对应字段，转换类型后，根据业务规则插入到目标表，用JOIN维护表之间的关联关系。如果你的源数据格式不是我假设的这样，把具体格式告诉我，我可以帮你调整转换函数的参数！

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