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+2 double(65,30) |
依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
# int 在 表 中的创建方法 create table 表名 values( 字段1 int, 字段2 int(显示宽度), 字段3 int unsigned, 字段4 int(显示宽度) unsigned ); # int默认是有符号的 # unsigned它能表示的数字的范围不被宽度约束 # unsigned它只能约束数字的显示宽度
# 创建一个 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)
# 创建 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)
