mysql数据库数据类型

         Mysql中不同的数据类型决定了mysql在存储他们时使用的方式以及在运算时选择的运算符，mysql支持的数据类型有多种，主要有数值类型、时间/日期类型和字符串类型。

1、数值类型

（1）整数类型

         Mysql中不仅支持标准SQL中的integer和smallint类型，还支持一些自己的扩展的整数类型，常用的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)、BIGINT,整数类型的属性字段可以添加”AUTO_INCREMENT”自增约束条件。常用的整数类型如下：

类型名称	说明	大小	有符号取值范围	无符号取值范围
TINYINT	很小整数	1字节	-128~127	0~255
SMALLINT	小整数	2字节	-32768~32767	0~65535
MEDIUMINT	中等大小整数	3字节	-8388608~8388607	0-16777125
INT(integer)	普通大小整数	4字节	-2147483648-2147483674	0-4294967295
BIGINT	大整数	8字节

         MySQL可以为整数类型指定宽度，这个限制对大多数应用没有意义，因为这不是限制值的合法范围，对于存储和计算来说，INT(1)和INT(20)是相同的，只是对一些MySQL的交互工具规定了显示字符的个数，比如MySQL命令行客户端。

（2）固定浮点类型

         Decimal数据类型用来存储高精度数据，一般只在对小数进行精确计算时才使用；numeric和decimal的含义相同。

         Decimal的使用方法为DECIMAL[(M[,D])] [UNSIGNED]；其中M代表精度表示可以使用多少位的数字,N代表小数点后的小数位数。但不需要小数时可以使用decimal(m),decimal(m,0)表示。M的最大取值范围是65,N的最大取值范围是30，当D为0时可以用来存储比BIGINT更大范围的整数值；当指定UNSIGEND时表示不允许负数。

（3）浮点类型

         浮点类型中包含float和double两种，与decimal相比是不精确类型，浮点型数据类型如下：

类型	说明	大小
FLOAT	单精度浮点数	4字节
DOUBLE	双精度浮点数	8字节
DECLIMAL	压缩的”严格”定点数	M+2个字节

         浮点类型float和double的用法同Decimal用法相同，在存储同样范围的值时，通常比decimal使用更少的空间。Float和double在不指定精度时，默认会按照实际的精度（由操作系统和系统决定）。

mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.12, 9876543.12, 9876543.12);
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from temp2;
+------------+------------+------------+
| id         | id2        | id3        |
+------------+------------+------------+
| 1234567.25 | 1234567.21 | 1234567.21 |
| 9876543.00 | 9876543.12 | 9876543.12 |
+------------+------------+------------+

2、日期与时间类型

         日期时间类型包括date,time,datetime,timestamp和year，用来指定不同范围的日期或时间值，主要的时间类型如下：

类型名称	作用	日期格式	范围	大小
YEAR	表示年	YYYY	1901-2155	1字节
TIME	表示时间	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	表示日期	YYYY-MM-DD	1000-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 00:00:01 UTC~ 2038-01-19 03:14:07 UTC	4字节

         Timestamp和datetime日期时间类型可以被自动初始化和更新为当前的日期时间数据，当默认指定current timestamp为默认值，或者指定此数据列为自动更新时（指定默认值是指当插入新的数据而该列没有显视指定数值时，则插入当前日期时间值；指定自动更新是指当行中的其他列被更新时，则此列被自动更新为当前日期时间值）

# timestamp不指定日期时插入当前的默认日期
mysql> create table temp3(id int,tstamp datetime,tstamp2 timestamp);
mysql> insert into temp3(id) value(1);
mysql> select * from temp3;
+------+--------+---------------------+
| id   | tstamp | tstamp2             |
+------+--------+---------------------+
|    1 | NULL   | 2018-09-29 03:02:51 |
+------+--------+---------------------+
# timestamp的日期时间会自动更新；datetime指定默认时间时会自动插入当前时间，使用update current_timestamp时会自动跟新为当前时间
mysql> create table temp4(id int,tstamp datetime default current_timestamp on update current_timestamp,tstamp2 datetime default current_timestamp ,tstamp3 timestamp,tstamp4 timestamp default current_timestamp on update current_timestamp);
mysql> insert into temp4(id) value(1);
mysql> select * from temp4;
+------+---------------------+---------------------+---------------------+---------------------+
| id   | tstamp              | tstamp2             | tstamp3             | tstamp4             |
+------+---------------------+---------------------+---------------------+---------------------+
|    1 | 2018-09-29 03:20:43 | 2018-09-29 03:20:43 | 2018-09-29 03:20:43 | 2018-09-29 03:20:43 |
+------+---------------------+---------------------+---------------------+---------------------+
mysql> update temp4 set id=2 where id=1;
mysql> select * from temp4;
+------+---------------------+---------------------+---------------------+---------------------+
| id   | tstamp              | tstamp2             | tstamp3             | tstamp4             |
+------+---------------------+---------------------+---------------------+---------------------+
|    2 | 2018-09-29 03:23:25 | 2018-09-29 03:20:43 | 2018-09-29 03:23:25 | 2018-09-29 03:23:25 |
+------+---------------------+---------------------+---------------------+---------------------+

         当在time,timestamp,datetime中指定含有毫秒微秒数值时，则用type_name(fsp)来表达，其中fsp可以取0到6之间的数值

mysql> create table frac_test(test1 TIME(2),test2 DATETIME(2),test3 TIMESTAMP(3));
mysql> INSERT INTO frac_test VALUES('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
mysql> SELECT * from frac_test;
+-------------+------------------------+-------------------------+
| test1       | test2                  | test3                   |
+-------------+------------------------+-------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.777 |
+-------------+------------------------+-------------------------+

3、字符类型

         字符类型主要用来存储字符数据，字符类型包含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^24
LONGTEXT	大的非二进制字符串	L+4字节，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值得数目（最大65535）
SET	字符串对象可有0个或多个SET成员	1、2、3、4、8个字节，取决于集合成员的数量（最多64个成员）

         Char类型的长度一旦指定就固定了，其范围可以是0到255，当被存储时，未达到指定长度的则在值右边填充空格，而获取数据时则会把右侧的空格去掉；char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。而且对非常短的字符串，char不需要一个额外的字节记录长度。

         Varchar类型是变长的类型，其范围可以是0到65535，当存储时未达到指定长度则不填充空格；同时varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节时，需要1个字节，否则需要2个字节；varchar节省了存储空间，所以对性能也有帮助。但由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。如果一行占用的空间增长，并且物理数据页内没有更多空间存储时，MyISAM会将行拆成不同的片段存储，InnoDB需要分列页来讲行放到数据页里。

mysql> CREATE TABLE char_test(test1 VARCHAR(4),test2 CHAR(4)); 
mysql> INSERT INTO char_test VALUES('abc ','abc ');  
mysql> mysql> SELECT CONCAT('(',test1,')'),CONCAT('(',test2,')') from char_test;
+-----------------------+-----------------------+
| CONCAT('(',test1,')') | CONCAT('(',test2,')') |
+-----------------------+-----------------------+
| (abc )                | (abc)                 |
+-----------------------+-----------------------+

4、二进制类型

         Binary和varbinary类型和char/varchar类似，用来存储二进制字符

5、大数据类型

         Blob和text类型被用来存储大量的数据，Blob和text的列字段不能含有默认值

         Blob是用来存储二进制的大量数据，其有四种类型：tinyblob、blob、mediumblob、longblob

         Text是用来存储字符型的大量数据，其有四种类型： tinytext、text、mediumtext、longtext

6、枚举类型

         Enum枚举类型是字符串类型，其值是从事先指定的一系列值中选出，适用在某列的取值范围已经固定，在存储此类数据时，直接转化成数字存储而不是字符串，可以节省空间，并且在表的.frm文件中存储“数字-字符串”之间的对应关系。

mysql> CREATE TABLE enum_test(name VARCHAR(40),size ENUM('x-small','small','medium','large','x-large'));
mysql> INSERT INTO enum_test(name,size) VALUES('dayi123','medium'),('dayi','small'),('hhh','x-large');
mysql> select size+0 from enum_test;
+--------+
| size+0 |
+--------+
|      3 |
|      2 |
|      4 |
+--------+

         枚举类型的排序规则是按照存储顺序进行而不是按照值本身排序，如果想要按照一般的排序规则进行排序，需要使用field()函数显示指定排序规则

mysql> select * from enum_test order by size;
+---------+--------+
| name    | size   |
+---------+--------+
| dayi    | small  |
| dayi123 | medium |
| hhh     | large  |
+---------+--------+
mysql> select * from enum_test order by field(size,'medium','large','small');
+---------+--------+
| name    | size   |
+---------+--------+
| dayi123 | medium |
| hhh     | large  |
| dayi    | small  |
+---------+--------+

         枚举类型字段的取值的增加必须通过alter table命令：

mysql> alter table enum_test modify size ENUM('x-small', 'small', 'medium', 'large', 'x-large','big');

         Enum枚举类型最多可以有65535个值，当插入数字到枚举类型字段时，数字会被当做枚举值的第几个值而插入，当插入一个非法的值到枚举字段时，则会报错，如果枚举字段允许NULL，则NULL值为此枚举类型的默认值。

7、集合类型

         Set集合类型是字符类型，可以含有0个或多个值，其中的每个值都需要在创建字段时指定的集合中，Set集合最大可以有255个值，MySQL在存储set集合时，同样也是存储为数字类型。集合时无序的。

mysql> CREATE TABLE set_test(set_col SET('a','b','c','d','e'));
mysql> INSERT INTO set_test(set_col) VALUES('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
mysql> SELECT * FROM set_test;
+---------+
| set_col |
+---------+
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a,d     |
+---------+

8、数据类型的选择

（1）选择小的数据类型

         尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU时间也更少

（2）选择简单的

         简单的数据类型操作通常需要更少的CPU周期。

（3）避免null

         因为如果查询中包含可为NULL的列，对MySQL来说更难优化，因为NULL的列使得索引、索引统计和值比较都更为复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，所以会使用更多的存储空间

9、数据库的设计方法与工具

（1）设计方法

         E-R模型，第三范式

（2）设计工具

         Powerdesigner、workbench