【SQL】利用sql语句在mysql的表中插入date类型的数据，

take it down

已于 2022-05-26 16:12:26 修改

阅读量3.5w

点赞数 10

分类专栏： SQL 文章标签： mysql sql 数据库

于 2019-07-06 23:16:05 首次发布

本文链接：https://blog.csdn.net/myydebk666/article/details/94911889

版权

Linux 同时被 2 个专栏收录

39 篇文章 0 订阅

订阅专栏

SQL

1 篇文章 0 订阅

订阅专栏

文中可能有错，请谨慎实施

一. 创建一个数据库

create database test

二. 在数据库中创建表

create TABLE employees (emp_no int(4) not null,birth_date date  not null,first_name varchar(14) not null,last_name varchar(16) not null,gender char(1) not null,hire_dare date not null,primary key (emp_no));

三. 表结构

mysql> desc employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(4)      | NO   | PRI | NULL    |       |
| birth_date | date        | NO   |     | NULL    |       |
| first_name | varchar(14) | NO   |     | NULL    |       |
| last_name  | varchar(16) | NO   |     | NULL    |       |
| gender     | char(1)     | NO   |     | NULL    |       |
| hire_dare  | date        | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.73 sec)

四. 插入记录

mysql> insert into employees values(2,1998-07-14,'li','bc','w',2019-02-21);
Query OK, 1 row affected, 2 warnings (0.00 sec)

可以看到有告警

五. 查看表内容

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_dare  |
+--------+------------+------------+-----------+--------+------------+
|      1 | 0000-00-00 | mai        | yp        | m      | 0000-00-00 |
|      2 | 0000-00-00 | li         | bc        | w      | 0000-00-00 |

插入date类型的字段失效

六. 解决：加单引号

mysql> insert into employees values(3,1998-07-14,'li','bc','w',20190211);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into employees values(4,1998-07-14,'li','bc','w',‘20190211’); 
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_dare  |
+--------+------------+------------+-----------+--------+------------+
|      1 | 0000-00-00 | mai        | yp        | m      | 0000-00-00 |
|      2 | 0000-00-00 | li         | bc        | w      | 0000-00-00 |
|      3 | 0000-00-00 | li         | bc        | w      | 0000-00-00 |
|      4 | 0000-00-00 | li         | bc        | w      | 2019-02-11 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)

参考：https://blog.csdn.net/lycyl/article/details/45689163