mysql 数据类型
整数类型
指明了具体的类型，存储消耗的字节数，最小最大取值范围， unsigned代表不允许负数，则正整数的取值范围扩大一倍
mysql> create table yj (id int unsigned); Query OK, 0 rows affected (0.02 sec)
MySQL可以为整数类型指定宽度，比如INT(11)， 这个限制对大多数应用没有意义，因为这不是限制值的合法范围，对于存储和计算来说，INT(1)和INT(20)是相同的，只是对一些MySQL的交互工具规定了显示字符的个数，比如MySQL命令行客户端
mysql> create table temp1(id int(1),id2 int(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into temp1 values(1000000,1000000); Query OK, 1 row affected (0.00 sec) mysql> select * from temp1; +---------+---------+ | id | id2 | +---------+---------+ | 1000000 | 1000000 | +---------+---------+ 1 row in set (0.00 sec) 01 mysql> alter table temp1 modify id int(1) zerofill; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table temp1 modify id2 int(20) zerofill; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from temp1; +------+----------------------+ | id | id2 | +------+----------------------+ | 10 | 00000000000000000010 | +------+----------------------+
固定浮点类型
Decimal和numeric数据类型用来存储高精度数据，一般只在对小数进行精确计算时才使用， 比如涉及财务数据的时候
DECIMAL[(M[,D])] [UNSIGNED]
在MySQL中， numeric和decimal的含义相同
Decimal的使用方法举例为decimal(5,2)
其中的5代表为精度，表示了可以使用多少位数字
其中的2代表小数点后面的小数位数
此例子的取值范围为-999.99到999.99
当不需要指定小数时，可以使用decimal(M),decimal(M,0)表示
当直接使用decimal时，则默认的M为10
M的最大取值为65， D的最大取值为30，当D为0时可以用来存储比BIGINT更大范围的整数值M的最大取值为65， D的最大取值为30，当D为0时可以用来存储比BIGINT更大范围的整数值
当指定unsigned，表示不允许负数
MySQL对decimal字段采用每4个字节存储9个数字的方式，例如decimal(18,9)小数点两边各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
浮点类型中包含float和double两种，与decimal相比是不精确类型
FLOAT[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数， D则代表小数点 后的小数位数
DOUBLE[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数， D则代表小数 点后的小数位数
在存储同样范围的值时，通常比decimal使用更少的空间， float使用4个字
节存储， double使用8个字节, decimal分字段采用每4个字节存储9个数字的方式 一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
例：
mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21,
-> 9876543.12, 9876543.12);
Query OK, 2 rows affected (0.01 sec)
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 rows in set (0.00 sec)
bit类型
Bit数据类型用来存储bit值
BIT(M)代表可以存储M个bit， M的取值范围为1到64
如果手工指定bit值，则可以使用b’value’格式，比如b’111’和
b‘10000000’分别代表7和128
除非特殊情况，否则尽量不要使用这个类型
日期时间类型
非法的date,datetime,timestamp值将被转换成0值， 0000-00-00或者0000-00-00 ，00:00:00
针对非法的year数据，则直接转化为0000
Timestamp和datetime日期时间类型可以被自动初始化和更新为当前的日期时间数据，当你默认指定current_timestamp为默认值，或者指定此数据列为自
动更新时
指定默认值是指当插入新的数据而该列没有显视指定数值时，则插入当前日期时间值
指定自动更新是指当行中的其他列被更新时，则此列被自动更新为当前日期时间值
timestamp 创建完第一列会默认加上默认值（default current_timestamp）和自动更新(on update current_timestamp)
mysql> create table t1(id int ,ts timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
#添加数据，自动把当前时间加上去(defult current_timestamp)
mysql> insert into t1 (id) values (12)
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+---------------------+
| id | ts |
+------+---------------------+
| 12 | 2018-08-10 00:32:59 |
+------+---------------------+
1 row in set (0.00 sec)
#修改数据会自动更新为修改数据的那个时间
mysql> update t1 set id=45 where id=12;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+---------------------+
| id | ts |
+------+---------------------+
| 45 | 2018-08-10 00:33:36 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
添加时间默认值和自动更新
create table temp4(id int,tstamp datetime default current_timestamp on update current_timestamp);
字符类型
字符类型包含char, varchar, binary, varbinary, blob, text, enum和set
Char和varchar可以通过char(M)和varchar(M)指定可以存储的最大字符数，比如char(30)表示可以存储最长30个字符Char类型的长度
一旦指定就固定了，如果超过30个字符按前30个字符截取，其范围可以是0到255，当被存储时，未达到指定长度的则在值右边填充空格，而获取数据时则会把右侧的空格去掉
Varchar类型是变长的类型，其范围可以是0到65535，当存储是未达到指定长度则不填充空格
Varchar它比定长类型更节省空间，因为它仅使用必要的空间。
另外varchar需要使用1或2个额外字节记录字符串的长度如果列的最大长度小于等于255字节时，需要1个字节，否则需要2个字节
比如采用Latin1字符集， varchar(10)的列需要11个字节的存储空间，而varchar(1000)列需要1002个字节的存储空间
char类型是定长， MySQL总是根据定义的字符串长度分配足够的空间。 当查询char值时， MySQL会删除所有的末尾空格
char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据， char也比varchar更好，因为定长的char类型不容易产生碎片。而且对非常短的字符串， char不需要一个额外的字节记录长度
二进制类型
Binary和varbinary类型和char/varchar类似，只不过是存储二进制字符
mysql> CREATE TABLE t (c BINARY(3)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t SET c = 'a'; Query OK, 1 row affected (0.01 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.09 sec) 0
大数据类型
Blob和text类型被用来存储大量的数据
Blob是用来存储二进制的大量数据，其有四种类型， tinyblob、 blob、 mediumblob、 longblob四种的区别是能存储的数据长度有所不同
Text是用来存储字符型的大量数据，其有四种类型， tinytext、 text、 mediumtext、 longtext四种的区别是能存储的数据长度有所不同
Blob和text的列字段不能含有默认值
枚举类型
Enum枚举类型是字符串类型，其值是从事先指定的一系列值中选出，适用在某列的取值范围已经固定
主要好处为MySQL在存储此类数据时，直接转化成数字存储而不是字符串，可以节省空间，并且在表的.frm文件中存储“数字-字符串”之间的对应关系
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name | size |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
select size+0 from shirts;
另外枚举类型的排序规则是按照存储顺序进行而不是按照值本身排序的
select size from shirts order by size;
mysql> select * from shirts order by size;
+-------------+--------+
| name | size |
+-------------+--------+
| polo shirt | small |
| t-shirt | medium |
| dress shirt | large |
如果想要按照一般的排序规则进行排序，需要使用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个值
当插入数字到枚举类型字段时，数字会被当做枚举值的第几个值而插入
mysql> delete from shirts; Query OK, 3 row affected (0.00 sec) mysql> insert into shirts (size) values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from shirts; +------+--------+ | name | size | +------+--------+ | NULL | x-mall | +------+--------+ 1 row in set (0.00 sec)
枚举类型中的空串和NULL值
当插入一个非法的值到枚举字段时， 则会报错
如果枚举字段允许NULL，则NULL值为此枚举类型的默认值
set 集合类型
Set集合类型是字符类型，可以含有0个或多个值，其中的每个值都需要是在创建字段时指定的集合中
比如一个字段被指定为SET(‘one’, ‘two’) not null可以含有以下四种值
- ”
- ‘one’
- ‘two’
- ‘one,two‘
Set集合最大可以有255个值
MySQL在存储set集合时，同样也是存储为数字类型
当存储一个数字到set集合字段时，就会按照二进制计算值
SET(‘a’,’b’,’c’,’d’)
如何选择数据类型
MySQL支持的数据类型很多，选择正确的数据类型对获得高性能至关重要
1. 更小的通常更好 尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用 更小的磁盘、内存和CPU缓存，并且处理时需要的CPU时间也更少 比如如果知道某个数字列的存储值在0~200之间，就应该选取tinyint类型
2. 简单就好 简单的数据类型操作通常需要更少的CPU周期。例如整型比字符操作代价更低，因为字 符集和排序规则使得字符比较比整型比较更复杂
3. 尽量避免NULL 通常情况下最好指定列为NOT NULL。因为如果查询中包含可为NULL的列，对MySQL来说 更难优化，因为可为NULL的列使得索引、索引统计和值比较都更为复杂。当可为NULL 的列被索引时，每个索引记录需要一个额外的字节，所以会使用更多的存储空间
自增类型字段
整型和浮点型字段可以被指定为自增长类型字段，意味着当插入行数据时这列为
NULL时，则按照此列最大值+1的方式插入数据
获取插入后的自增长列的值，可以用LAST_INSERT_ID()函数获取
一个表中只能有一个自增长字段，且不能含有默认值
自增长字段的数值从1开始递增，且不能插入负值
当你显视的插入一个数值到自增长字段时，则下一个是表中所有值的最大值+1
设置字段的auto_increment属性，可以有两种方法
Create table的时候指定
Alter table的时候指定： mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
针对Myisam存储引擎， auto_increment属性可以添加到多列键值的第二列上，则
自增列的值计算是根据第一个列分组计算得出
设置数据类型的默认值
Default默认值用来指定一个列的默认值，但不能指定函数或表达式作为默认值，比
如now()和current_date，但唯一的例外是可以指定current_timestamp作为timestamp
和datetime列的默认值
Blob,text列不能指定默认值
如果一个列没有显视指定default默认值，则依照以下规则
如果该列允许null值，则默认值为null
