MySQL数据类型

一、数字类型

A：整型

mysql> show create table a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

这里的10，表示什么意思

本身没有意义，只有与zerofill配合在一起，才会起作用

mysql> create table c( a int(3) zerofill,b int(3) zerofill);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into c select 1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from c;
+------+------+
| a | b |
+------+------+
| 001 | 002 |
+------+------+
1 row in set (0.00 sec)

mysql> select a-b from c;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`testDB`.`c`.`a` - `testDB`.`c`.`b`)'

mysql> insert into c select 1111;
ERROR 1136 (21S01): Column count doesn't match value count at row 1 （列计数不匹配值计数）

INT类型的属性：

　　UNSIGNED/SIGNED: 是否有符号

　　ZEROFILL: 显示属性，值不做任何修改

　　Auto_INCREMENT：自增，每张表一个自增字段，该自增字段，必须是索引的一部分

mysql> create table d ( a int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key （自增字段必须是一个索引（key），否则会报错）
mysql> create table d ( a int auto_increment primary key);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into d select NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from d;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

总结：

1、推荐不要试用unsigned，unsigned可能会有溢出现象发生

2、自增int类型，主键建议使用bigint类型

TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

案例：

mysql> create table t1 (i1 tinyint,i2 tinyint unsigned);
Query OK, 0 rows affected (0.17 sec)

mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)（当设置为严格模式时，此时插入数据，就报错）

mysql> insert into t1(i1,i2) values(256,256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> set sql_mode='ANSI'; （修改sql_mode为宽松模式）
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t1(i1,i2) values(256,256);
Query OK, 1 row affected, 2 warnings (0.02 sec)

B：

单精度类型： FLOAT

双精度类型： DOUBLE

高精度类型：DECIMAL

注意：

1、财务类型必须使用DECIMAL类型

2、float(M,D)/DOUBLE(M,D)/DECIMAL(M,D),M表示多少位整数，其中D表示小数点后面有几位

3、mysql在保存值的时候，会进行四舍五入，例如float（7,4），当插入，999.00009 这个时候会显示999.0001

mysql> create table t( a decimal);
Query OK, 0 rows affected (0.14 sec)

mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table f( a decimal(7,3)); ----总共7位，小数点后面占3位
Query OK, 0 rows affected (0.18 sec)

mysql> insert into f value(3457.234);
Query OK, 1 row affected (0.03 sec) 显示 3457.234

mysql> insert into f value(1324.2744);
Query OK, 1 row affected, 1 warning (0.01 sec) 显示1324.274

mysql> insert into f value(1324.2747);
Query OK, 1 row affected, 1 warning (0.05 sec) 显示1324.275

mysql> select * from f;
+----------+
| a |
+----------+
| 3457.234 |
| 1324.274 |
| 1324.275 |
+----------+
3 rows in set (0.00 sec)

二、字符类型

char(N)、Varchar(N) 这里的N，指定的是字符个数

BINARY(N),VARBINARY(N) 这里的N，表示字节个数

通过SHOW character set可以查看mysql数据库支持的字符集，例如

mysql> show character set;

注意：

1、在BLOB和TEXT列上创建索引时，必须指定索引前缀的长度

2、BLOB和TEXT列不能有默认值

3、BLOB和TEXT列排序只使用该列的前max_sort_length

mysql> select @@global.max_sort_length;
+--------------------------+
| @@global.max_sort_length |
+--------------------------+
| 1024 |
+--------------------------+
1 row in set (0.00 sec)

字符集介绍

　　常见的字符集： utf8，utf8mb4，gbk，gb18030

修改字符集

mysql> create database aa default character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use aa
Database changed
mysql> create table a ( id int);
Query OK, 0 rows affected (0.09 sec)

mysql> show create table a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

字符串类型-----ENUM & SET

ENUM（‘男’，‘女’）

ENUM类型最多运行65536个值

SET类型最多允许64个值

mysql> create table b ( user varchar(30),sex ENUM('male','female'));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into b select 'david','male';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into b select 'john','bmale';
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

三、日期类型

timestamp和datatime的区别：

　　timestamp引入时区的概念

　　在建表时，列为timestamp的日期类型可以设置一个默认值，而datatime不行

案例：

mysql> create table c (a timestamp,b datetime);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into c select now(),now();
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from c;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2017-11-27 17:02:08 | 2017-11-27 17:02:08 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select time_zone;
ERROR 1054 (42S22): Unknown column 'time_zone' in 'field list'
mysql> select @@time_zone; 系统时区，东八区
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.00 sec)

mysql> set time_zone="+00:00" 当修改时区后，则发现时间就不一样
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from c;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2017-11-27 09:02:08 | 2017-11-27 17:02:08 |
+---------------------+---------------------+
1 row in set (0.00 sec)

日期函数

NOW（）返回sql执行的时间

current_timestamp 与now函数同义

sysdate 返回执行函数的时间

date_add（date，interval expr unit）增加时间

date_sub（date，interval expr unit）减少时间

mysql> select now(),sysdate(),sleep(5),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now() | sysdate() | sleep(5) | now() | sysdate() |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2017-11-27 09:35:10 | 2017-11-27 09:35:10 | 0 | 2017-11-27 09:35:10 | 2017-11-27 09:35:15 |
+---------------------+---------------------+----------+---------------------+---------------------+

mysql> select date_add(now(),interval 5 day);
+--------------------------------+
| date_add(now(),interval 5 day) |
+--------------------------------+
| 2017-12-02 09:37:45 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval -5 day);
+---------------------------------+
| date_add(now(),interval -5 day) |
+---------------------------------+
| 2017-11-22 09:38:24 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> create table a ( a datetime default current_timestamp on update current_timestamp,b char(10));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into a (b) values ('sa'),('sdf'),('yf');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

然后select查询，发现日期为当前时间

mysql> select * from a;
+---------------------+------+
| a | b |
+---------------------+------+
| 2017-11-28 13:38:59 | sa |
| 2017-11-28 13:38:59 | sdf |
| 2017-11-28 13:38:59 | yf |
+---------------------+------+
3 rows in set (0.00 sec)

四、JSON类型