SQL数据类型

1、


如果超出类型范围的操作，会发生“Out of range”错误提示。

2、对于整型数据，MySQL支持在类型名称后面的小括号内指定显示宽度，不指定显示宽度则默认为int(11)，zerofill则表示用字符“0”填满宽度。
alter table t1 modify id1 int zerofill;
所有的整数类型都有一个可选属性UNSIGNED（无符号）。
如果一个列指定为zerofill，则自动为该列添加UNSIGNED属性。

3、在需要产生唯一标识符或顺序值时，可利用AUTO_INCREMENT，一般从1开始，每行增加1。在插入NULL到一个AUTO_INCREMENT列时，MySQL插入一个比该列中当前最大值大1 的值。一个表中最多只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。定义方式：
CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL, PRIMARY KEY);
CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(ID));
CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL, UNIQUE(ID));

4、小数分为浮点数和定点数。浮点数包括float单精度和double双精度，而定点数只有decimal，它以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后加“（M，D)"的方式表示，该值一共显示M为数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。MySQL在保存值时进行四舍五入。这是非标准用法。

5、float和double在不指定精度时，默认会按照实际的精度来显示，而decimal在不指定精度时，默认的整数位为10，小数位为0.
创建测试表：
create table ‘t1’(‘id1’ float(5,2) default NULL,‘id2’ double(5,2) default
id3’ decimal(5,2) default NULL);
default NULL 表示默认值为NULL。
插入数据1.23；
insert into t1 values(1.23,1.23,1.23);
前两个插入1.234，最后一个插入1.23；
insert into t1 values(1.234,1.234,1.23);
id1和id2由于标度的限制，舍去了最后一位。
同时插入数据1.234；
insert into t1 values(1.234,1.234,1.234);
虽然数据都插入进去，但是系统出现了一个warning，报告id3被阶段，如果在传统的SQLmode，这条记录是无法插入的。
将id1，id2，id3字段的精度和标度全部去掉，再次插入数据1.23；
alter table t1 modify id1 float;
alter table t1 modify id2 double;
alter table t1 modify id3 decimal;
id1,id2可以正常插入数据，而id3字段的小数位被截断。
**：浮点数如果不写精度和标度会按照实际精度值显示，如果写了，则会自动将四舍五入后的结果插入，系统不会报错。定点数如果不写精度和标度，则按照默认值decimal（10,0）来进行操作，如果数据超越了精度和标度值，系统则会报错。**错。

6、对于BIT（位）类型，用于存放位字段值,BIT(M)可以用来存放多位二进制数，M范围从1~61，不写则默认为1.对于位字段，直接使用select命令看不到结果，可以用bin（）显示为二进制格式或者hex（）显示为十六进制格式进行读取。
select bin(id),hex(id) from t2;

7、数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的位数，则插入失败。

8、时间类型类型

年月日-date，年月日时分秒-datetime，时分秒-time。

如果需要经常插入或者更新日期为当前系统时间，通常使用TIMESTAMP来表示，返回后显示为”YYYY-MM-DD HH:MM:SS"格式的字符串，显示宽度固定为19个字符，如果想获得数字值则应该在TIMESTAMP列添加“+0”。

9、只表示年度用YEAR，4位格式，1901-2155.
如果超出有效值范围，系统会进行错误提示并将以零值来进行存储。

10、创建日期表：
create table t(d date, t time,dt datetime);
用now（）函数插入当前日期：
insert into t values(now(),now(),now());

创建表，字段为TIMESTAMP类型：
create table t(id1 timestamp);
insert into t values(NULL);

TIMESTAMP插入日期时会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。

create table t(id1 timestamp not null default current_timestamp,id2 datetime default null);
查看当前时区；
show variables like ‘time_zone’；
修改时区为东九区：
set time_zone=’+9:00’;

11、timestamp的取值范围从19700101080001到2038年后的某一天，因此不适合存放比较久远的日期。

12、timestamp和datetime的区别：
1）timestamp支持的时间范围较小，datetime范围是从1000-01-01 00:00:00到9999-12-31 23:59:59；范围更大。
2）表中的第一个timestamp列自动设置为系统时间，如果在一个timestamp列中插入NULL，则该列值将自动设置为当前的日期和时间。在插入或更新一行但不明确给timestamp列赋值时也会自动设置该列的值为当前的日期和时间，当插入的值超出取值范围时，MySQL认为该值溢出，使用“0000-00-00 00:00:00”进行填补。
3）timestamp的插入和查询都受当地时区的影响，更能反映出实际的日期。而datetime则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差。
4)timestamp的属性受MySQL版本和服务器SQLMode的影响很大。

13、字符串类型

14、char和varchar都用来保存较短的字符串，主要区别在于存储方式的不同，char列的长度固定为创建表时声明的长度，0~255；varchar列中的值为可变长字符串。在检索的时候，char列删除了尾部的空格，而varchar则保留。

15、binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串;
mysql> create table t(c binary(3));
Query OK, 0 rows affected (0.64 sec)

mysql> insert into t set c=‘a’;
Query OK, 1 row affected (0.18 sec)

mysql> select *,hex©,c=‘a’,c=‘a\0’,c=‘a\0\0’ from t;
±-----±-------±------±--------±----------+
| c | hex© | c=‘a’ | c=‘a\0’ | c=‘a\0\0’ |
±-----±-------±------±--------±----------+
| a | 610000 | 0 | 0 | 1 |
±-----±-------±------±--------±----------+
1 row in set (0.00 sec)
保存binary值时会在值的最后通过填充“0x00”零字节以达到指定的字段定义长度。

16、ENUM类型叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，1_{255个成员的枚举需要1个字节存储，255}65535个成语需要2个字节存储，最多允许65535个成员。
mysql> create table t(gender enum(‘M’,‘F’));
Query OK, 0 rows affected (0.45 sec)

mysql> insert into t values(‘M’),(‘1’),(‘f’),(NULL);
Query OK, 4 rows affected (0.18 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t;
±-------+
| gender |
±-------+
| M |
| M |
| F |
| NULL |
±-------+
4 rows in set (0.00 sec)
可以看出ENUM类型是忽略大小写的，对于插入不在ENUM指定范围内的值时并没有返回警告，而是出入了enum（’M‘，’F‘）的第一个值“M”。
ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

17、SET类型与ENUM类型相似，也是字符串对象，可以包含0_{64个成员。1}8成员的集合占1个字节，9_{16占2个，17}24占3个，25_{32占4个，33}64占8个。
SET和ENUM 除了存储之外最主要的区别在于SET类型一次可以选取多个成员，而ENUM类型则只能选一个。
mysql> create table t(col set(‘a’,‘b’,‘c’,
-> ‘d’));
Query OK, 0 rows affected (0.45 sec)

mysql> insert into t values(‘a,b’),(‘a,d,a’),(‘a,b’),(‘a,c’),(‘a’);
Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t;
±-----+
| col |
±-----+
| a,b |
| a,d |
| a,b |
| a,c |
| a |
±-----+
5 rows in set (0.00 sec)
对于超出允许值范围的值例将不允许注入，而对于（’a，d，a‘）这样包含重复成员的集合将只取一次。