mysql数据类型自增_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)

mysql> show warnings

-> ;

+---------+------+---------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------+

| Warning | 1264 | Out of range value for column 'i1' at row 1 |

| Warning | 1264 | Out of range value for column 'i2' at row 1 |

+---------+------+---------------------------------------------+

2 rows in set (0.00 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> show character set like 'gb%';

+---------+---------------------------------+--------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+---------------------------------+--------------------+--------+

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

+---------+---------------------------------+--------------------+--------+

3 rows in set (0.00 sec)

mysql> show character set like 'utf8%';

+---------+---------------+--------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+---------------+--------------------+--------+

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

+---------+---------------+--------------------+--------+

2 rows in set (0.00 sec)

修改字符集

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类型

相关函数