cse mysql_Mysql数据类型

数值类型

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

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

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

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

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

注意：对于整型为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

类型大小范围(有符号)范围(无符号)用途

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 字节

(-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 字节

(-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

依赖于M和D的值

依赖于M和D的值

小数值

整型使用：

========================================

tinyint[(m)] [unsigned] [zerofill]

小整数，数据类型用于保存一些范围的整数数值范围：

有符号：

-128 ～ 127

无符号：

～ 255

PS： MySQL中无布尔值，使用tinyint(1)构造。

========================================

int[(m)][unsigned][zerofill]

整数，数据类型用于保存一些范围的整数数值范围：

有符号：

-2147483648 ～ 2147483647

无符号：

～ 4294967295

========================================

bigint[(m)][unsigned][zerofill]

大整数，数据类型用于保存一些范围的整数数值范围：

有符号：

-9223372036854775808 ～ 9223372036854775807

无符号：

～ 18446744073709551615

mysql> create table t2(x tinyint);

Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| x | tinyint(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into t2 values(-129),(-128),(127),(128);

ERROR 1264 (22003): Out of range value for column 'x' at row 1

mysql>

mysql> insert into t2 values(-128),(127);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t2;

+------+

| x |

+------+

| -128 |

| 127 |

+------+

2 rows in set (0.00 sec)

mysql> create table t3(x tinyint unsigned);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(255),(0),(-1);

ERROR 1264 (22003): Out of range value for column 'x' at row 3

mysql>

mysql> insert into t3 values(255),(0);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t3;

+------+

| x |

+------+

| 255 |

| 0 |

+------+

2 rows in set (0.00 sec)

mysql> create table t4(x int); #默认为有符号，即数字前有正负号

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4 values(-2147483648),(2147483647);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t4;

+-------------+

| x |

+-------------+

| -2147483648 |

| 2147483647 |

+-------------+

2 rows in set (0.00 sec)

mysql> insert into t4 values(-2147483648),(2147483649);

ERROR 1264 (22003): Out of range value for column 'x' at row 2

mysql>

mysql> create table t5(x int(3) zerofill);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t5 values(1),(11),(111),(11111);

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t5;

+-------+

| x |

+-------+

| 001 |

| 011 |

| 111 |

| 11111 | #超过宽度限制仍然可以存

+-------+

4 rows in set (0.00 sec)

mysql> desc t5;

+-------+--------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------------------+------+-----+---------+-------+

| x | int(3) unsigned zerofill | YES | | NULL | |

+-------+--------------------------+------+-----+---------+-------+

1 row in set (0.01 sec)

mysql> desc t4;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

默认的显示宽度，都是在最大值的基础上加1

浮点型使用

======================================

#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

定义：

单精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号：

-3.402823466E+38 to -1.175494351E-38,

1.175494351E-38 to 3.402823466E+38

无符号：

1.175494351E-38 to 3.402823466E+38

精确度：

**** 随着小数的增多，精度变得不准确 ****

======================================

#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义：

双精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号：

-1.7976931348623157E+308 to -2.2250738585072014E-308

2.2250738585072014E-308 to 1.7976931348623157E+308

无符号：

2.2250738585072014E-308 to 1.7976931348623157E+308

精确度：

****随着小数的增多，精度比float要高，但也会变得不准确 ****

======================================

decimal[(m[,d])] [unsigned] [zerofill]

定义：

准确的小数值，m是数字总个数(负号不算)，d是小数点后个数。 m最大值为65，d最大值为30。

精确度：

**** 随着小数的增多，精度始终准确 ****

对于精确数值计算时需要用此类型

decaimal能够存储精确值的原因在于其内部按照字符串存储。

mysql> create table t6(x float(256,31));

ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.

mysql> create table t6(x float(255,30));

Query OK, 0 rows affected (0.01 sec)

mysql> create table t7(x double(255,30));

Query OK, 0 rows affected (0.01 sec)

mysql> create table t8(x decimal(66,30));

ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65.

mysql> create table t8(x decimal(65,30));

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> insert into t6 values(1.1111111111111111111111111111111);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 values(1.1111111111111111111111111111111);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values(1.1111111111111111111111111111111);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> ^Csert into t7 values(1.1111111111111111111111111111111);

^C

mysql>

mysql>

mysql> select * from t6; #随着小数的增多，精度开始不准确

+----------------------------------+

| x |

+----------------------------------+

| 1.111111164093017600000000000000 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select * from t7; #精度比float要准确点，但随着小数的增多，同样变得不准确

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111200000000000000 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select * from t8; #精度始终准确,d为30，于是只留了30位小数

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111111111111111111 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select length(x) from t8;

+-----------+

| length(x) |

+-----------+

| 32 |

+-----------+

1 row in set (0.00 sec)

总结：

float：在位数比较短的情况下不精准(**** 数值越大，越不准确 ****)

double：在位数比较长的情况下不精准(**** 数值越大，越不准确 ****)

decimal：如果是小数，则推荐使用decimal

因为精准，内部原理是以字符串的形式去存

日期和时间类型

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

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

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

表示时间值的日期和时间类型为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

混合日期和时间值，时间戳

有下面几种类型：

datatime:2017-09-06 10:30:22

data:2017-09-06

time:10:30:22

year:2017

timeatamp:和datatime是一样的，就是有时区的特性

create table t4(

id int primary key auto_increment,

born_year year,

born_date date,

born_dt datetime,

born_time time

);

insert into t4(born_year,born_date,born_dt,born_time) values(now(),now(),now(),now());

COMMIT;

select * from t4

字符串类型

字符串类型指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 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

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

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

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

char:简单粗暴，不够就用空格凑够固定长度存放起来，浪费空间，但是存储速度快

(牺牲空间，提高速度)

varchar(你有几个就存几个):精准，计算出待存放数据的长度，节省空间，存取速度慢

(牺牲速度，提高效率)

枚举与集合

字段的值只能在给定范围中选择，如单选框，多选框

enum枚举：规定一个范围：这个范围可以有多个，但是为该字段传值时，只能取规定范围内的其中一个

set集合：规定一个范围：这个范围可以有多个，但是为该字段传值时，可以取规定范围内的一个或多个

enum如果你不传值，默认是第一个值，或者为NUll

create table student(

id int primary key auto_increment,

name char(5),

sex enum('male','female'), #enum 代表枚举类型

hobbies set('eat','play','study','coding') #set 代表集合类型

);

insert into student(name,sex,hobbies) values('yy','none','eat');

COMMIT;

select * from student; #如果设置了sex是枚举类型，就的从设定的里面选其中的一个存

insert into student(name,sex,hobbies) values('xx','female','play,study');

COMMIT;

select * from student; #如果设置了hobbies是集合类型，就得从设定的里面选其中一个或者多个值来存