MySQL(数据类型和完整约束)

MySQL数据类型

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

1.数值数据类型

包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小树类型DECIMAL。

2.日期/时间类型

包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

3.字符串类型

包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。

1. 整数类型

数值型数据类型主要用来存储数字，MYSQL提供了多种数值数据类型，不同的数据库有不同的取值范围，可以存储的值范围越大，其所存储的控件也会越大。MySQL主要提供整数类型有：TINYTINT、SMALLINT、MEDIUMINT、INT、BIGINT。整数类型属性的字段可以添加AUTO_INCREMENT自增约束条件。下表列出了MySQL中的数值类型。

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等大小的整数	3个字节
INT	普通大小的整数	4个字节
BIGINT	大整数	8个字节

从表可知，不同类型整数存储所需的字节数是不同的，占用字节数最小的是TINYINT类型，占用字节最大的是BIGINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据的取值范围，例如TINYINT需要1个字节(8bits)来存储，那么TINYINT无符号数的最大值为2^8-1，即255；TINYINT有符号数的最大值为2^7-1，即127。其它类型如下表所示：

数据类型	有符号	无符号
TINYINT	-128~127	0~255
SMALLINT	-32768~32767	0~65535
MEDIUMINT	-8388608~8388607	0~16777215
INT	-2147483648~2147483647	0~4294967295
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615

　　1.2 浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度（FLOAT）和双精度（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M，N）来表示，其中M称为精度，表示总共的位数；N称为标度，是表示小数的位数。下表列出了小数类型存储的需求。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL(M,D),DEC	压缩的”严格”定点数	M+2个字节

DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则取值范围将随M的变大而变大。

1.3 日期/时间类型

MySQL中有多种表示日期的数据类型，主要有：DATETIME、DATE、TIMESTAMP、TIME和YEAR。下表列出了MySQL日期/时间类型：

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC	4字节

　　1.4 字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串之外，还可以存储其它数据，比如图片和声音二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。下表列出了字符串数据类型。

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节,1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节,在此L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节,在此L<2^32
LONGTEXT	大的非二进制字符串	L+4字节,在此L<2^24
ENUM	枚举类型,只能有一个枚举字符串值	1或2个字节,取决于枚举值数目(最大值65535)
SET	一个设置,字符串对象可以有0个或多个SET成员	1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员）

　　1.5 二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串。MySQL中存储二进制字符串数据类型有：BIT、BINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。下表列出了二进制数据类型：

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8个字节
BINARY(M)	固定长度二进制字符串	M个字节
VARBINARY(M)	可变长度二进制字符串	M+1个字节
TINYBLOB(M)	非常小的BLOB	L+1个字节,在此L<2^8
BLOB(M)	小BLOB	L+2字节,在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节,在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4字节,在此L<2^32

如何选择数据类型

MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应该使用精确的类型。即在所有可以表示该列值的类型中，该类型使用的存储最少。

2.1 整数和浮点数

如果不需要小数部分，则使用整数来保存数据；如果需要小数部分，则使用浮点数来表示。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，如果列的值范围为1-99999，若使用整数，则MEDIUMINT UNSIGNED是最好的类型；若需存储小数，则使用FLOAT类型。

浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此，如要求存储精度较高时，应选择DOUBLE。

2.2 浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。DECIMAL在MySQL中是以字符串存储的。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出现问题，因此在进行计算的时候，一定要小心。如果进行数值比较，建议使用DECIMAL类型。

2.3 日期/时间类型

MySQL对于不同种类的日期和时间有多种数据类型，比如YEAR和TIME。如果只需记录年份，则使用YEAR即可，如果只记录时间，则使用TIME类型。

如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP这个列取值时范围小于DATETIME的取值范围，因此存储范围较大的日期最好使用DATETIME。

TIMESTAMP也有一个DATETIME不具备的属性。默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空��上比DATETIME更有效。

2.4 CHAR与VARCHAR之间的特点与选择

CHAR与VARCHAR之间的区别：

CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会自动删除插入数据的尾部空格，VARCHAR不删除尾部空格。

CHAR是固定长度，所以它的处理速度比VARCHAR速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。

存储引擎对CHAR和VARCHAR的影响：

对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用存储空间换查询时间。

对于InnoDB存储引擎：使用可变长的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际存的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

2.5 ENUM和SET

ENUM只能取单值，它的数据列表示一个枚举集合。它的合法值列表最多有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。比如：性别字段适合定义成ENUM类型，每次只能从’男’ 或 ‘女’中取一个值。

SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型，比如：要存储一个兴趣爱好，最好使用SET类型。

ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。

2.6 BLOB和TEXT

BLOB是二进制字符串，TEXT是非二进制字符串，二者均可存放大容量信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件，应分清二者存储的关系。

完整约束

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

分类及详解

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK)    标识该字段为该表的外键

NOT NULL    标识该字段不能为空

UNIQUE KEY (UK)    标识该字段的值是唯一的

AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT    为该字段设置默认值

UNSIGNED 无符号

ZEROFILL 使用0填充

. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值

. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'

age int unsigned NOT NULL default  必须为正值（无符号） 不允许为空 默认是20

. 是否是key

主键 primary key

外键 foreign key

索引 (index,unique...)

类型一 not null和default

not null - 不可空

default默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

==================not null====================

mysql> create table t1(id int); #id字段默认可以插入空

mysql> desc t1;

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

| Field | Type    | Null | Key | Default | Extra |

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

| id    | int() | YES  |     | NULL    |       |

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

mysql> insert into t1 values(); #可以插入空

mysql> create table t2(id int not null); #设置字段id不为空

mysql> desc t2;

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

| Field | Type    | Null | Key | Default | Extra |

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

| id    | int() | NO   |     | NULL    |       |

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

mysql> insert into t2 values(); #不能插入空

ERROR  (HY000): Field 'id' doesn't have a default value

==================default====================

#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

mysql> create table t3(id int default );

mysql> alter table t3 modify id int not null default ;

==================综合练习====================

mysql> create table student(

    -> name varchar() not null,

    -> age int() unsigned not null default ,

    -> sex enum('male','female') default 'male',

    -> hobby set('play','study','read','music') default 'play,music'

    -> );

mysql> desc student;

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

| Field | Type                               | Null | Key | Default    | Extra |

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

| name  | varchar()                        | NO   |     | NULL       |       |

| age   | int() unsigned                    | NO   |     |          |       |

| sex   | enum('male','female')              | YES  |     | male       |       |

| hobby | set('play','study','read','music') | YES  |     | play,music |       |

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

mysql> insert into student(name) values('egon');

mysql> select * from student;

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

| name | age | sex  | hobby      |

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

| egon |   | male | play,music |

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

示例验证

类型二 unique（设置唯一约束）

方法一：

create table department1(

id int,

name varchar() unique,

comment varchar()

);

方法二：

create table department2(

id int,

name varchar(),

comment varchar(),

constraint uk_name unique(name)

);

mysql> insert into department1 values(,'IT','技术');

Query OK,  row affected (0.00 sec)

mysql> insert into department1 values(,'IT','技术');

会报错  怎么解决呢？？？

ERROR  (): Duplicate entry 'IT' for key 'name'

设置唯一约束的方法

约束not null和unique结合的神奇之处：

mysql> create table t1(id int not null unique);

Query OK,  rows affected (0.02 sec)

mysql> desc t1;

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

| Field | Type    | Null | Key | Default | Extra |

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

| id    | int() | NO   | PRI | NULL    |       |

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

 row in set (0.00 sec)

其实Notnull和unique结合就相当于把上面示例中的id字段设置为主键

create table service(

id int primary key auto_increment,

name varchar(),

host varchar() not null,

port int not null,

unique(host,port) #联合唯一

);

mysql> insert into service values

    -> (,'nginx','192.168.0.10',),

    -> (,'haproxy','192.168.0.20',),

    -> (,'mysql','192.168.0.30',)

    -> ;

Query OK,  rows affected (0.01 sec)

Records:   Duplicates:   Warnings: 

将主键设置为host和port俩个字段

设置联合唯一主键

类型三 primary key设置主键

primary key 字段的值不为空且唯一可设置为主键(单列做主键多列做主键(符合主键))

单列主键：

============单列做主键===============

#方法一：not null+unique

create table department1(

id int not null unique, #主键

name varchar() not null unique,

comment varchar()

);

mysql> desc department1;

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

| Field   | Type         | Null | Key | Default | Extra |

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

| id      | int()      | NO   | PRI | NULL    |       |

| name    | varchar()  | NO   | UNI | NULL    |       |

| comment | varchar() | YES  |     | NULL    |       |

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

rows in set (0.01 sec)

#方法二：在某一个字段后用primary key

create table department2(

id int primary key, #主键

name varchar(),

comment varchar()

);

mysql> desc department2;

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

| Field   | Type         | Null | Key | Default | Extra |

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

| id      | int()      | NO   | PRI | NULL    |       |

| name    | varchar()  | YES  |     | NULL    |       |

| comment | varchar() | YES  |     | NULL    |       |

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

rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key

create table department3(

id int,

name varchar(),

comment varchar(),

constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;

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

| Field   | Type         | Null | Key | Default | Extra |

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

| id      | int()      | NO   | PRI | NULL    |       |

| name    | varchar()  | YES  |     | NULL    |       |

| comment | varchar() | YES  |     | NULL    |       |

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

rows in set (0.01 sec)

多列主键：

==================多列做主键================

create table service(

ip varchar(),

port char(),

service_name varchar() not null,

primary key(ip,port)

);

mysql> desc service;

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

| Field        | Type        | Null | Key | Default | Extra |

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

| ip           | varchar() | NO   | PRI | NULL    |       |

| port         | char()     | NO   | PRI | NULL    |       |

| service_name | varchar() | NO   |     | NULL    |       |

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

 rows in set (0.00 sec)

mysql> insert into service values

    -> ('172.16.45.10','','mysqld'),

    -> ('172.16.45.11','','mariadb')

    -> ;

Query OK,  rows affected (0.00 sec)

Records:   Duplicates:   Warnings: 

mysql> insert into service values ('172.16.45.10','','nginx');

ERROR  (): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

类型四 auto_increment

设置自增字段：

#不指定id，则自动增长

create table student(

id int primary key auto_increment,

name varchar(),

sex enum('male','female') default 'male'

);

mysql> desc student;

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

| Field | Type                  | Null | Key | Default | Extra          |

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

| id    | int()               | NO   | PRI | NULL    | auto_increment |

| name  | varchar()           | YES  |     | NULL    |                |

| sex   | enum('male','female') | YES  |     | male    |                |

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

mysql> insert into student(name) values

    -> ('egon'),

    -> ('alex')

    -> ;

mysql> select * from student;

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

| id | name | sex  |

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

|   | egon | male |

|   | alex | male |

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

#也可以指定id

mysql> insert into student values(,'asb','female');

Query OK,  row affected (0.00 sec)

mysql> insert into student values(,'wsb','female');

Query OK,  row affected (0.00 sec)

mysql> select * from student;

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

| id | name | sex    |

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

|   | egon | male   |

|   | alex | male   |

|   | asb  | female |

|   | wsb  | female |

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

#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

mysql> delete from student;

Query OK,  rows affected (0.00 sec)

mysql> select * from student;

Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');

mysql> select * from student;

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

| id | name | sex  |

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

|   | ysb  | male |

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

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

mysql> truncate student;

Query OK,  rows affected (0.01 sec)

mysql> insert into student(name) values('egon');

Query OK,  row affected (0.01 sec)

mysql> select * from student;

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

| id | name | sex  |

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

|   | egon | male |

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

 row in set (0.00 sec)

设置自增字段的起始值和自增步长：

#在创建完表后，修改自增字段的起始值

mysql> create table student(

    -> id int primary key auto_increment,

    -> name varchar(),

    -> sex enum('male','female') default 'male'

    -> );

mysql> alter table student auto_increment=;

mysql> show create table student;

.......

ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8

mysql> insert into student(name) values('egon');

Query OK,  row affected (0.01 sec)

mysql> select * from student;

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

| id | name | sex  |

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

|   | egon | male |

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

row in set (0.00 sec)

mysql> show create table student;

.......

ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8

#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外

create table student(

id int primary key auto_increment,

name varchar(),

sex enum('male','female') default 'male'

)auto_increment=;

#设置步长

sqlserver：自增步长

    基于表级别

    create table t1（

        id int。。。

    ）engine=innodb,auto_increment= 步长= default charset=utf8

mysql自增的步长：

    show session variables like 'auto_inc%';

    #基于会话级别

    set session auth_increment_increment= #修改会话级别的步长

    #基于全局级别的

    set global auth_increment_increment= #修改全局级别的步长（所有会话都生效）

#！！！注意了注意了注意了！！！

If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略

比如：设置auto_increment_offset=，auto_increment_increment=

mysql> set global auto_increment_increment=;

Query OK,  rows affected (0.00 sec)

mysql> set global auto_increment_offset=;

Query OK,  rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录

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

| Variable_name            | Value |

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

| auto_increment_increment |      |

| auto_increment_offset    |      |

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

create table student(

id int primary key auto_increment,

name varchar(),

sex enum('male','female') default 'male'

);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');

mysql> select * from student;

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

| id | name  | sex  |

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

|   | egon1 | male |

|   | egon2 | male |

|  | egon3 | male |

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

类型五 foreign key

　　单独作为一个知识点来讲述详见foreign key详解文章

秒客网