mysql8学习笔记24--MySQL数据库设计--数据类型之字符类型、二进制类型、大数据类型、枚举类型、集合类型、如何选择、自增长类型字段
数据类型之字符类型
• 字符类型包含char, varchar, binary, varbinary, blob, text, enum和set
• Char和varchar可以通过char(M)和varchar(M)指定可以存储的最大字符数，比如char(30)表示可以存储最长30个字符Char类型的长度一旦指定就固定了，其范围可以是0到255，当被存储时，未达到指定长度的则在值右边填充空格，而获取数据时则会把右侧的空格去掉
• Varchar类型是变长的类型，其范围可以是0到65535，当存储是未达到指定长度则不填充空格
mysql> create table temp7(name1 char(255),name2 varchar(65535));#varchar的长度，与使用的字符编码相关 ERROR 1074 (42000): Column length too big for column 'name2' (max = 21845); use BLOB or TEXT instead mysql> create table temp7(name1 char(255),name2 varchar(21845)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> create table temp7(name1 char(255),name2 varchar(21845)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> mysql> mysql> create table temp7(name1 char(255),name2 varchar(8126)); Query OK, 0 rows affected (0.14 sec) mysql> create table temp7(name1 char(255),name2 varchar(8127)); ERROR 1050 (42S01): Table 'temp7' already exists mysql> drop table temp7; Query OK, 0 rows affected (0.08 sec) mysql> create table temp7(name1 char(255),name2 varchar(8127)); Query OK, 0 rows affected (0.12 sec) mysql> drop table temp7; Query OK, 0 rows affected (0.12 sec) mysql> create table temp7(name1 char(256),name2 varchar(8127)); ERROR 1074 (42000): Column length too big for column 'name1' (max = 255); use BLOB or TEXT instead mysql> drop table temp7; ERROR 1051 (42S02): Unknown table 'school.temp7' mysql> create table temp7(name1 char(255),name2 varchar(8127)); Query OK, 0 rows affected (0.07 sec) mysql> show variables like '%character%'; +--------------------------+-------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /mysql8/mysql/share/charsets/ | +--------------------------+-------------------------------+ 8 rows in set (0.08 sec) mysql>
• Varchar类型用来存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间。
• 另外varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节时，需要1个字节，否则需要2个字节
• 比如采用Latin1字符集，varchar(10)的列需要11个字节的存储空间，而varchar(1000)列需要1002个字节的存储空间。
• varchar节省了存储空间，所以对性能也有帮助。但由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。如果一行占用的空间增长，并且物理数据页内没有更多空间存储时，MyISAM会将行拆成不同的片段存储，InnoDB需要分列页来讲行放到数据页里。
• char类型是定长，MySQL总是根据定义的字符串长度分配足够的空间。当查询char值时，MySQL会删除所有的末尾空格
• char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。而且对非常短的字符串，char不需要一个额外的字节记录长度
• Char类型值右边的空格会被自动剔除，而varchar类型则不会
注：当然，实际上Char存储在底层空间中，当插入的字符串没达到限定的长度时，也是会补上空格的。
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO vc VALUES ('ab ', 'ab '); Query OK, 1 row affected (0.06 sec) mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc; +---------------------+---------------------+ | CONCAT('(', v, ')') | CONCAT('(', c, ')') | +---------------------+---------------------+ | (ab ) | (ab) | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql>
数据类型之二进制类型
• Binary和varbinary类型和char/varchar类似，只不过是存储二进制字符
mysql> CREATE TABLE t (c BINARY(3)); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO t SET c = 'a'; Query OK, 1 row affected (0.00 sec) mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t; +--------+---------+-------------+ | HEX(c) | c = 'a' | c = 'a\0\0' | +--------+---------+-------------+ | 610000 | 0 | 1 | +--------+---------+-------------+ 1 row in set (0.00 sec) mysql>
数据类型之大数据类型
• Blob和text类型被用来存储大量的数据
• Blob是用来存储二进制的大量数据，其有四种类型，tinyblob、blob、mediumblob、longblob四种的区别是能存储的数据长度有所不同
• Text是用来存储字符型的大量数据，其有四种类型， tinytext、text、mediumtext、longtext四种的区别是能存储的数据长度有所不同
• Blob和text的列字段不能含有默认值
数据类型之枚举类型
• Enum枚举类型是字符串类型，其值是从事先指定的一系列值中选出，适用在某列的取值范围已经固定
• 主要好处为MySQL在存储此类数据时，直接转化成数字存储而不是字符串，可以节省空间，并且在表的.frm文件中存储“数字-字符串”之间的对应关系
mysql> CREATE TABLE shirts (name VARCHAR(40),size ENUM('x-small', 'small', 'medium', 'large', 'x-large')); Query OK, 0 rows affected (0.14 sec) mysql> INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small'); Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT name, size FROM shirts WHERE size = 'medium'; +---------+--------+ | name | size | +---------+--------+ | t-shirt | medium | +---------+--------+ 1 row in set (0.00 sec) mysql> UPDATE shirts SET size = 'small' WHERE size = 'large'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) mysql> select size+0 from shirts; +--------+ | size+0 | +--------+ | 2 | | 3 | | 2 | +--------+ 3 rows in set (0.00 sec) mysql>
• 另外枚举类型的排序规则是按照存储顺序进行而不是按照值本身排序的
mysql> select size from shirts order by size; +--------+ | size | +--------+ | small | | small | | medium | +--------+ 3 rows in set (0.00 sec) mysql> select * from shirts order by size; +-------------+--------+ | name | size | +-------------+--------+ | dress shirt | small | | polo shirt | small | | t-shirt | medium | +-------------+--------+ 3 rows in set (0.00 sec) mysql>
• 如果想要按照一般的排序规则进行排序，需要使用field()函数显示指定排序规则
• select size from shirts order by field(size,’large’,’medium’,’small’)
• 枚举类型字段的取值的增加必须通过alter table命令
• mysql> alter table shirts modify size ENUM('x-small', 'small', 'medium', 'large', 'x-large','xx-large');
• Enum枚举类型最多可以有65535个值
• 当插入数字到枚举类型字段时，数字会被当做枚举值的第几个值而插入
• 枚举类型中的空串和NULL值
• 当插入一个非法的值到枚举字段时，则会报错
• 如果枚举字段允许NULL，则NULL值为此枚举类型的默认值
数据类型之集合类型
• Set集合类型是字符类型，可以含有0个或多个值，其中的每个值都需要是在创建字段时指定的集合中
• 比如一个字段被指定为SET(‘one’, ‘two’) not null可以含有以下四种值
• ''
• 'one'
• 'two'
• 'one,two‘
• Set集合最大可以有255个值
• MySQL在存储set集合时，同样也是存储为数字类型
• 当存储一个数字到set集合字段时，就会按照二进制计算值
• SET('a','b','c','d')
• 当该列插入9这个数字时，则转化为二进制的1001，即转化为’a’,’d’值
• Set集合列中各值的顺序无关紧要，且一个值如果出现多次也会被忽略
数据类型之如何选择
• MySQL支持的数据类型很多，选择正确的数据类型对获得高性能至关重要
• 更小的通常更好
• 尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU时间也更少
• 比如如果知道某个数字列的存储值在0~200之间，就应该选取tinyint类型
• 简单就好
• 简单的数据类型操作通常需要更少的CPU周期。例如整型比字符操作代价更低，因为字符集和排序规则使得字符比较比整型比较更复杂
• 尽量避免NULL
• 通常情况下最好指定列为NOT NULL。因为如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更为复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，所以会使用更多的存储空间
• Default默认值用来指定一个列的默认值，但不能指定函数或表达式作为默认值，比如now()和current_date，但唯一的例外是可以指定current_timestamp作为timestamp和datetime列的默认值
• Blob,text列不能指定默认值
• 如果一个列没有显视指定default默认值，则依照以下规则
• 如果该列允许null值，则默认值为null
自增长类型字段
• 整型和浮点型字段可以被指定为自增长类型字段，意味着当插入行数据时这列为NULL时，则按照此列最大值+1的方式插入数据
• 获取插入后的自增长列的值，可以用LAST_INSERT_ID()函数获取
• 一个表中只能有一个自增长字段，且不能含有默认值
• 自增长字段的数值从1开始递增，且不能插入负值
mysql> CREATE TABLE animals (id MEDIUMINT NOT NULL AUTO_INCREMENT,name CHAR(30) NOT NULL,PRIMARY KEY (id)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO animals (name) VALUES('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ 6 rows in set (0.00 sec) mysql>
• 当你显视的插入一个数值到自增长字段时，则下一个是表中所有值的最大值+1
• 设置字段的auto_increment属性，可以有两种方法
• Create table的时候指定
• Alter table的时候指定： mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
• 针对Myisam存储引擎，auto_increment属性可以添加到多列键值的第二列上，则自增列的值计算是根据第一个列分组计算得出
