系列文章:MySQL 基础:库表、约束与类型 的第 3 篇。
约束
实际上分为两种,表约束和列约束。列属性的约束上面已经讲述了,现在来讲述下表约束。
列约束
- 在之前的
插入表数据(一)中已经演示了5种常用的约束,下面主要介绍表之前的约束。
外键约束
- 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
- 数据表的存储引擎只能是InnoDB。
- 外键列和参照列必须具有相同的数据类型,其中数字的长度或是否有符号
UNSIGNED必须一致;而字符的长度可以不同。 - 外键列和参照列必须创建索引,如果没有创建Mysql也会自动创建。
由于本人开发中,使用外键的场景比较少,所以这里最后在补充。
数据库存储引擎
数据库默认的存储引擎可以在 my.cnf 中设置。
default-storage-engine=INNODB
Mysql可以将数据以不同的技术存储在文件(内存)中,这种技术就成为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
引擎类型
- MyISAM
- InnoDB
- Memory
- CSV
- Archive
而确定使用那种引擎取决与引用场景。
- 并发控制:
当多个连接对记录经行修改时候,保证数据的一致性和完整性 - 锁:共享锁(读锁)、排它锁(写锁)。
读锁同一个时间内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。写锁在任何时候只能有一个用户能写入资源,当其他写锁时会阻塞其他读锁或则写锁操作。- 锁颗粒
- 表锁(开销很小的锁策略)、行锁(开销很大的锁策略)。
- 事务(ACID):
保证数据库的完整性- 原子性
- 一致性
- 隔离性
- 持久性
- 索引
- 并发控制:
| 特点 | MyISAM | InnoDB | Memory | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | 64TB | 有 | 无 |
| 事务安全 | - | 支持 | - | - |
| 支持索引 | 支持 | 支持 | 支持 | - |
| 锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
| 数据压缩 | 支持 | - | - | 支持 |
| 支持外键 | - | 支持 | - | - |
看到这张表很清楚了,这就是为什么InnoDB用的很广泛的原因。
Mysql数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它们决定了数据存储的格式,代表了不同的数据信息类型。我们的计算都是二进制来存储数据的,当然这还牵扯到一个问题就是正数和负数。
数据类型思维导图

数据类型存储范围说明
所谓的无符号就是unsigned,这样会使大小翻倍。
整型
- TINYINT
迷你整数型- 字节:
1
- 字节:
- SMALLINT
小整型- 字节:
2
- 字节:
- MEDIUMINT
中整型- 字节:
3
- 字节:
- INT
标准整型- 字节:
4
- 字节:
- BIGINT
大整型- 字节:
8
- 字节:
用Java代码测试下。
public static void main(String[] args) {
System.out.println("采用移位运算所以次方所以相比次方的数值会减一");
System.out.println("TINTITN 有符号 "+ (-2L << 6) + " 到 " +((2L << 6) - 1));
System.out.println("TINTITN 无符号 0 到 "+(Math.pow(2L,8) - 1));
System.out.println("SMALLINT 有符号 "+ (-2L << 14) + " 到 " +((2L << 14) - 1));
System.out.println("SMALLINT 无符号 0 到 "+((2L << 14) - 1));
System.out.println("MEDIUMINT 有符号 "+ (-2L << 22) + " 到 " +((2L << 22) - 1));
System.out.println("MEDIUMINT 无符号 0 到 "+((2L << 23) - 1));
System.out.println("INT 有符号 "+ (-2L << 30) + " 到 " +((2L << 30) - 1));
System.out.println("INT 无符号 0 到 "+((2L << 31) - 1));
System.out.println("BIGINT 有符号 "+ (-2L << 62) + " 到 " +((2L << 62) - 1));
System.out.println("BIGINT 无符号 0 到 "+ ((2L << 63) - 1));
}
输出
采用移位运算所以次方所以相比次方的数值会减一
TINTITN 有符号 -128 到 127
TINTITN 无符号 0 到 255.0
SMALLINT 有符号 -32768 到 32767
SMALLINT 无符号 0 到 32767
MEDIUMINT 有符号 -8388608 到 8388607
MEDIUMINT 无符号 0 到 16777215
INT 有符号 -2147483648 到 2147483647
INT 无符号 0 到 4294967295
BIGINT 有符号 -9223372036854775808 到 9223372036854775807
BIGINT 无符号 0 到-1
# 这里可以看到BIGINT 无符号Java long数据类型已经满足不了了。
在数据优化的原则上,合理的选择存储数据类型。而不是盲目的选择数据类型大。比如我们的年龄用TINTINT 就够了。也就说在指定数据类型的时候,一般是采用从小原则,这样会对MYSQL的效率提高很多。当然做好设计。
SQL测试
# 创建int类型的表看看。
[root@localhost][Demo]> CREATE TABLE test_int(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
-> int_1 TINYINT ,
-> int_2 SMALLINT ,
-> int_3 MEDIUMINT ,
-> int_4 INT ,
-> int_5 BIGINT
-> );
Query OK, 0 rows affected (0.07 sec)
# 查看表结构。
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| int_1 | tinyint(4) | YES | | NULL | |
| int_2 | smallint(6) | YES | | NULL | |
| int_3 | mediumint(9) | YES | | NULL | |
| int_4 | int(11) | YES | | NULL | |
| int_5 | bigint(20) | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
#插入测试1,整型插入。成功。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,1,2,3,4,5);
Query OK, 1 row affected (0.01 sec)
#插入测试2,用单引号包裹数字。竟然成功了。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,'1','2','3','4','5');
Query OK, 1 row affected (0.01 sec)
#插入测试3,用字符串测试。肯定是插入不成功。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,'a','b','c','d','e');
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'int_1' at row 1
#测试下有符号的情况,这里提示int_1 超过范围了。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,-200,2,3,4,5);
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1
#修改参数试试,成功了。TINYINT 有符号 是 -128 到 127。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,-128,2,3,4,5);
Query OK, 1 row affected (0.00 sec)
#修改 int_1 的属性 修改成UNSIGNED。无符号。注意在修改字段属性的时候,如果已经存在数据请确认存在的数据是否有不合法的。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED;
ERROR 1264 (22003): Out of range value for column 'int_1' at row 3
#为了继续测试,删除掉表中数据。
[root@localhost][Demo]> DELETE FROM test_int;
Query OK, 4 rows affected (0.03 sec)
#重新修改表字段结构。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 检查是否修改成功。可以看到 int_1 添加了UNSIGNED属性约束了。
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| int_1 | tinyint(3) unsigned | YES | | NULL | |
| int_2 | smallint(6) | YES | | NULL | |
| int_3 | mediumint(9) | YES | | NULL | |
| int_4 | int(11) | YES | | NULL | |
| int_5 | bigint(20) | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
# 插入TINYINT 最大值试试,OK这里是成功的。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,255,2,3,4,5);
Query OK, 1 row affected (0.00 sec)
# 当超过TINYINT UNSIGNED最大的时候就会抛出异常信息。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,256,2,3,4,5);
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1
这里就演示TINYINT存储范围的情况了,实际上其它证书型基本类似。
实际上有一点需要注意就是TYPE中的花括弧中的数字是干嘛的
# 这个3个是干嘛的呢?
tinyint(3) unsigned
这实际上是显示宽度的意思。当显示的宽度不够的时候可以使用0来填充,使用zreofuill进行零填充,而且零填充会直接导致该数值变成服务号。
# 演示修改表结构,将int_1字段再次修改。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED ZEROFILL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#检查表结构是否发生变化
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| int_1 | tinyint(3) unsigned zerofill | YES | | NULL | |
| int_2 | smallint(6) | YES | | NULL | |
| int_3 | mediumint(9) | YES | | NULL | |
| int_4 | int(11) | YES | | NULL | |
| int_5 | bigint(20) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
# 插入新的数据
[root@localhost][Demo]> INSERT test_int VALUES(NULL,1,2,3,4,5);
Query OK, 1 row affected (0.01 sec)
# 刚才插入的int_1位置的值已经发生了变化。
[root@localhost][Demo]> SELECT * FROM test_int;
+----+-------+-------+-------+-------+-------+
| id | int_1 | int_2 | int_3 | int_4 | int_5 |
+----+-------+-------+-------+-------+-------+
| 5 | 255 | 2 | 3 | 4 | 5 |
| 6 | 001 | 2 | 3 | 4 | 5 |
+----+-------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
填充零的含义更多的就是保证数据的格式。
浮点型
- FLOAT
单精度浮点型- 字节:
4
- 字节:
- DOUBLE
双精度浮点型- 字节:
8
- 字节:
在SQL中,将小数型分配两种
浮点型和定点型两种。
- 浮点型使用注意事项,如果直接使用
float,则标识没有小数部分,如果用了float(M,D),其中M代表长度,D代表小数部分长度,M-D则为整数部分长度。
SQL测试
# 创建测试表test_float
[root@localhost][Demo]> CREATE TABLE test_float(
-> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
-> float_1 float,
-> float_2 float(6,2),
-> float_3 float(4,1)
-> );
Query OK, 0 rows affected (0.07 sec)
# 检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM test_float;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| float_1 | float | YES | | NULL | |
| float_2 | float(6,2) | YES | | NULL | |
| float_3 | float(4,1) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#插入模拟数据
[root@localhost][Demo]> INSERT INTO test_float VALUES(NULL,2.1534,11,888.1);
Query OK, 1 row affected (0.00 sec)
#这里需要注意的时候 float(4,2)实际标识的整数位小于4,而不是小于等于。测试下
[root@localhost][Demo]> INSERT INTO test_float VALUES(NULL,2.1534,11,8888.1);
ERROR 1264 (22003): Out of range value for column 'float_3' at row 1
# 结果很明显,8888.1 插入失败。所以插入的时候也需要注意。
定点型
- DECIMAL
定点数- 字节:
3
- 字节:
定点型数据,绝对的保证整数部分不会被四舍五入,也就是说不会损失经度,但是小数部分有可能损失精度,虽然理论上小数部分也不会丢失精度。
SQL测试
# 与float比较,看看是否会四舍五入。
[root@localhost][Demo]> CREATE TABLE test_decimal(
-> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> float_1 float(10,2),
-> decimal_1 decimal(10,2)
-> );
Query OK, 0 rows affected (0.09 sec)
# 测试模拟数据
[root@localhost][Demo]> INSERT test_decimal VALUES(NULL,99999999.99 , 99999999.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
# 果然啊,float的被四舍五入了,而decimal保留的小数位。
[root@localhost][Demo]> SELECT * FROM test_decimal;
+----+--------------+-------------+
| id | float_1 | decimal_1 |
+----+--------------+-------------+
| 2 | 100000000.00 | 99999999.99 |
+----+--------------+-------------+
1 row in set (0.00 sec)
日期类型
- YEAR
年份- 字节:
1
- 字节:
- TIME
时间- 字节:
3
- 字节:
- DATE
日期- 字节:
3
- 字节:
- DATETIME
日期时间- 字节:
8
- 字节:
- TIMESTAMP
时间戳- 字节:
4
- 字节:
由于存在跨时区的问题,所以一般都是存数字日期,日期类型用的相对会比较少。
- 补充类型概况
DATATIME:日期时间,其格式为yyyy-MM-dd HH:mm:ss,表示的范围是从 1000 年到 9999 年,有零值,即0000-00-00 0000:00;DATA:日期,就是datetime的date部分;TIME:时间,或者说是时间段,为指定的某个时间区间之间,包含正负时间;TIMESTAMP:时间戳,但并不是真正意义上的时间戳,其是从1970年开始计算的,格式和datetime一致;YEAR:年份,共有两种格式,分别为year(2)和year(4).
SQL测试
# 创建一日期测试表 test_date
[root@localhost][Demo]> CREATE TABLE test_date(
-> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> date_1 DATETIME,
-> date_2 DATE,
-> date_3 TIME,
-> date_4 YEAR,
-> date_5 TIMESTAMP
-> );
Query OK, 0 rows affected (0.04 sec)
#检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM test_date;
+--------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+-----------------------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| date_1 | datetime | YES | | NULL | |
| date_2 | date | YES | | NULL | |
| date_3 | time | YES | | NULL | |
| date_4 | year(4) | YES | | NULL | |
| date_5 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
# 添加测试数据
[root@localhost][Demo]> INSERT INTO test_date VALUES(NULL,'2017-05-06 13:15:00','2017-05-06','13:15:00',2018,'2017-05-06 13:15:00');
Query OK, 1 row affected (0.00 sec)
# 检查数据结果
[root@localhost][Demo]> SELECT * FROM test_date;
+----+---------------------+------------+----------+--------+---------------------+
| id | date_1 | date_2 | date_3 | date_4 | date_5 |
+----+---------------------+------------+----------+--------+---------------------+
| 1 | 2017-05-06 13:15:00 | 2017-05-06 | 13:15:00 | 2018 | 2017-05-06 13:15:00 |
+----+---------------------+------------+----------+--------+---------------------+
1 row in set (0.01 sec)
# 验证下时间戳是否会自动更新。
[root@localhost][Demo]> UPDATE test_date SET date_4 = 2017 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 检查下结果
[root@localhost][Demo]> SELECT * FROM test_date;
+----+---------------------+------------+----------+--------+---------------------+
| id | date_1 | date_2 | date_3 | date_4 | date_5 |
+----+---------------------+------------+----------+--------+---------------------+
| 1 | 2017-05-06 13:15:00 | 2017-05-06 | 13:15:00 | 2017 | 2018-05-02 04:36:31 |
+----+---------------------+------------+----------+--------+---------------------+
1 row in set (0.00 sec)
# 这里可以看到 date_5 已经发生了变化。
字符型
- CHAR(M)
- 字节:
M个字节,0 <= M <= 255
- 字节:
- VARCHAR(M)
- 字节:
L + 1个字节 , 其中L <= M 且 0 <= M <= 65535
- 字节:
- TINYTEXT
- 字节:
L + 1个字节, 其中L < 2^8。
- 字节:
- TEXT
- 字节:
L + 2个字节,其中L < 2^16。
- 字节:
- MEDIUMTEXT
- 字节:
L + 3个字节,其中L < 2^24。
- 字节:
- LONGTEXT
- 字节:
L + 4个字节,其中 L < 2^32。
- 字节:
- ENMU('value1','value2')
- 字节:
1或者2字节,取决与枚举值的个数(最多65535个值)。
- 字节:
- SET('value1','value2')
- 字节:
1、2、3、4或者8字节,取决与Set成员的数目(最多64个成员)
- 字节:
这里涉及到定长和变长,比如说CHAR(M),如果存储的是ABC但是没有达到指定的长度就会在ABC后面添加空格直至满足长度大小。
字符串分类
- 定长:
CHAR - 变长:
VARCHAR - 文本:
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT - 枚举:
ENMU - 集合:
SET
小结
主键唯一且非空,外键维护引用完整性;整型按范围选宽度,金额用 decimal,日期与字符类型按精度和排序规则取舍。