MySQL数据库字段

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

一、数值类型

二、字符类型

三、时间类型

四、set和enum类型

会在下一章节的约束中介绍

五、开发中常用的字段类型

• 1、字符类型中常用VARCHAR
• 2、时间数据类型中常用DATETIME和TIMESTAMP
• 3、数值类型常用的INT、FLOAT、DUBLE

• 4、使用timestamp创建默认时间create_at timestamp default current_timestamp

  mysql> create table person(
      -> id int primary key auto_increment comment "用户id",
      -> name varchar(100) not null unique comment "用户名",
      -> city varchar(100) not null comment "城市",
      -> create_at timestamp default current_timestamp
      -> );
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> insert into person(name,city) values("张三", "广东"), ("李四", "广西");
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  mysql> select * from person;
  +----+--------+--------+---------------------+
  | id | name   | city   | create_at           |
  +----+--------+--------+---------------------+
  |  1 | 张三   | 广东   | 2018-06-29 13:49:24 |
  |  2 | 李四   | 广西   | 2018-06-29 13:49:24 |
  +----+--------+--------+---------------------+
  2 rows in set (0.00 sec)
  
  mysql>
  

• 5、使用datetime创建默认时间create_at datetime default now()

  mysql> create table person(
      -> id int primary key auto_increment comment "用户id",
      -> name varchar(100) not null unique comment "用户名",
      -> city varchar(100) not null comment "城市",
      -> create_at datetime default now()
      -> );
  Query OK, 0 rows affected (0.01 sec)
  mysql> desc person;
  +-----------+--------------+------+-----+-------------------+----------------+
  | Field     | Type         | Null | Key | Default           | Extra          |
  +-----------+--------------+------+-----+-------------------+----------------+
  | id        | int(11)      | NO   | PRI | NULL              | auto_increment |
  | name      | varchar(100) | NO   | UNI | NULL              |                |
  | city      | varchar(100) | NO   |     | NULL              |                |
  | create_at | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
  +-----------+--------------+------+-----+-------------------+----------------+
  4 rows in set (0.00 sec)
  
  mysql> insert into person(name, city) values("张三", "广东"), ("李四", "广西");
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  mysql> select * from person;
  +----+--------+--------+---------------------+
  | id | name   | city   | create_at           |
  +----+--------+--------+---------------------+
  |  1 | 张三   | 广东   | 2018-06-29 13:55:51 |
  |  2 | 李四   | 广西   | 2018-06-29 13:55:51 |
  +----+--------+--------+---------------------+
  2 rows in set (0.00 sec)
  
  mysql>