MySQL(二)--数据类型

MySQL数据类型

MySQL存储引擎类型

# 存储引擎
    # MyISAM: 适合做读 插入数据比较频繁的，对修改和删除涉及少的，
            # 不支持事务，行级锁和外键，有表级锁
            # 索引和数据分开存储，mysql5.5以下默认的存储引擎
            # 存储文件的个数：表结构、表中的数据、索引

    # InnoDB：适合并发比较高的，对事物一致性要求高的，
            # 相对更适应频繁的修改和删除操作，有行级锁，外键且支持事务
            # 索引和数据是存在一起的，【mysql5.6以上默认的存储引擎】
            # 存储文件的个数：表结构、表中的数据
            
    # MEMORY：数据存在内存中，表结构存在硬盘上，
            # 优势：增删改查速度快，
            # 劣势：重启数据消失、容量有限

    # 查看数据库所支持的存储引擎：
            show engines;

    # 查看当前库配置信息
            show variables like '%engine%';
            show variables like '%chara%';

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

类型	大小	范围（有符号）	范围（无符号）unsigned约束	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节float(255,30)	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节double(255,30)	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2，否则为D+2double(65,30)	依赖于M和D的值	依赖于M和D的值	小数值

-- int 在 表 中的创建方法
create table 表名 values(
    字段1 int,
    字段2 int(显示宽度),
    字段3 int unsigned,
    字段4 int(显示宽度) unsigned
);

-- int默认是有符号的
-- unsigned它能表示的数字的范围不被宽度约束
-- unsigned它只能约束数字的显示宽度

​

int 示例

# 创建一个 t1 表，使字段it2长度为5
mysql> create table t1(id1 int,it2 int(5));
Query OK, 0 rows affected (0.33 sec)

# 向 t1 表中插入一条数据
mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.13 sec)

# 显示 t1 表中所有数据,发现数据显示正常
mysql> select * from t1;
+------+------+
| id1  | it2  |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

# 再插入一条长的数据，看看能否显示完全
mysql> insert into t1 values(111111,222222);
Query OK, 1 row affected (0.22 sec)

# 查看结果发现并不是只显示5位
mysql> select * from t1;
+--------+--------+
| id1    | it2    |
+--------+--------+
|      1 |      2 |
| 111111 | 222222 |
+--------+--------+
2 rows in set (0.00 sec)

​

int的unsigned作用

# 创建 t2 表并使 id1字段无符号，表示数字的范围不被宽度约束
mysql> create table t2(id1 int(4) unsigned,id2 int(5));
Query OK, 0 rows affected (0.73 sec)

# id1可以正常插入
mysql> insert into t2 values(2147483648,2147483647);
Query OK, 1 row affected (0.13 sec)

# id2超出int数字范围发出警告，默认取最大范围值
mysql> insert into t2 values(2147483647,2147483648);
Query OK, 1 row affected, 1 warning (0.13 sec)

mysql> select * from t2;
+------------+------------+
| id1        | id2        |
+------------+------------+
| 2147483648 | 2147483647 |
| 2147483647 | 2147483647 |
+------------+------------+
2 rows in set (0.00 sec)

-- 小数的使用

-- 创建带有 小数 的表
create table 表名(
    字段1 float,  -- 默认单精度，小数位很少
    字段2 double,  -- 默认双精度，写什么显示什么
    字段3 decimal,  -- 默认只显示整数
);

-- 创建带有 指定小数 的表，当设置指定小数位数时，会根据指定位数的后一位小数做四舍五入然后显示
create table 表名(
    字段1 float(一共显示的位数,小数部分占几位), 
    字段2 double(一共显示的位数,小数部分占几位),
    字段3 decimal(一共显示的位数,小数部分占几位),
);

​

小数示例

# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入1.23，结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
row in set (0.00 sec)

# 向表中插入1.234，会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
rows in set (0.00 sec)

# 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
rows in set (0.00 sec)

# 建新表去掉参数约束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)

# 分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 发现decimal默认值是(10,0)的整数
mysql> select * from t3;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
row in set (0.00 sec)

# 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
+---------+--------------------+------+
| id1     | id2                | id3  |
+---------+--------------------+------+
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
+---------+--------------------+------+
rows in set (0.00 sec)

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时分秒
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	年月日时分秒
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

-- 时间类型
-- 创建 时间相关 的表
create table 表名(
    字段1 year,  -- 显示年
    字段2 time,  -- 显示时分秒
    字段3 date,  -- 显示年月日
    字段4 datetime,  -- 显示年月日时分秒
    字段5 timestamp  -- 显示年月日时分秒,默认不能为空,如果为空会自取当前时间
);

-- 增加数据，数字方式，位数必须够；now()取当前时间
insert into 表名 values(2019,221010,20191128,20191128221000,now());

-- 增加数据，字符串格式化方式，格式化字符为系统能识别的即可('-','+'等)
insert into 表名 values(2019,'22:10:10','2019-11-28','2019-11-28 22:10:00',now());

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR列的长度固定为创建表是声明的长度,范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535)。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

ENUM和SET类型

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。

SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

类型	大小	用途
ENUM	对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储;最多允许65535个成员。	单选：选择性别
SET	1-8个成员的集合，占1个字节9-16个成员的集合，占2个字节17-24个成员的集合，占3个字节25-32个成员的集合，占4个字节33-64个成员的集合，占8个字节	多选：兴趣爱好

-- 创建表
create table 表名(
    gender enum('男','女'),
    hobby set('吉他','计算机','手机')
)charset='utf8';  -- 使用utf8字符集识别中文

-- 插入数据
mysql> insert into 表名 values('男','吉他,手机,跑步');  -- 如果hobby中没有指定值则不显示

-- 查看表
mysql> select * from t4;
+--------+---------------+
| gander | hobby         |
+--------+---------------+
| 男     | 吉他,手机     |
+--------+---------------+
1 row in set (0.00 sec)