请选择 进入手机版 | 继续访问电脑版

牛大大的个人博客

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 1295|回复: 2

MySQL 数据类型

[复制链接]

149

主题

201

帖子

643

积分

版主

Rank: 7Rank: 7Rank: 7

积分
643
发表于 2020-5-10 21:36:03 | 显示全部楼层 |阅读模式
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。下面的表显示了需要的每个整数类型的存储和范围。
类型
大小
范围(有符号)
范围(无符号)
用途
TINYINT1 byte(-128,127)(0,255)小整数值
SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 bytes(-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)单精度
浮点数值
DOUBLE8 bytes(-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的值小数值

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型
大小
( bytes)
范围
格式
用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4
1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS混合日期和时间值,时间戳

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型
大小
用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。

回复

使用道具 举报

149

主题

201

帖子

643

积分

版主

Rank: 7Rank: 7Rank: 7

积分
643
 楼主| 发表于 2020-5-10 22:23:55 | 显示全部楼层
本帖最后由 牛大舅 于 2020-5-10 22:52 编辑

整数型。
整数型用于存放整型数据,在Sql中因为更多地要考虑如何节省空间,系统将整型又细分为以下5类。

  a)、tinyint:迷你整型。使用1个字节进行存储,  有符号范围:-128到127 ,无符号:0到255
  b)、smallint:小整型。使用2个字节进行存储,   有符号范围:-32768到32767 ,无符号:0到65535
  c)、mediumint:中整型。使用3个字节存储。     有符号范围:-8388608到8388607 ,无符号:0到16777215
  d)、int:标准整型。使用4个字节存储(常用)。    有符号范围:-2147483648到2147483647 ,无符号:0到4294967295
  e)、bigint:大整型。使用8个字节进行存储。     有符号范围:无符号:0到18446744073709551615

指定宽度:
  1. Create table my_tinyint1(            score tinyint(1) unsigned     )charset utf8;
复制代码
插入数据:insert into my_tinyint1 values (255); 这个时候插入的数据是255也不会报错(这种控制不会改变数据本身的大小)。
显示宽度的意义在于:当数据不够显示宽度的时候,会自动让数据变成对应的显示宽度(保证数据格式)。
如果给的是2位,插入的时候插入的是1位,要显示宽度为2位,通常需要搭配一个前导 0 来增加宽度,不改变值大小,用 zerofill 填充。零填充会导致数值自动变成无符号。
  1. create table my_tinyint2(       score tinyint(2) zerofill   )charset utf8;
复制代码
插入数据:insert into my_tinyint2 values(5);
                score:05



小数型。
      Sql中,将小数型又细分为两种,浮点型和定点型。
       浮点型:
            浮点型数据是一种精度型数据,因为超出指定范围之后,会丢失精度(自动四舍五入)
            height  float:直接使用 float 表示没有小数部分。
      money float(M,D):M 代表总长度,D代表小数部分长度。整数部分长度为 M-D。
            浮点型数据的插入,整型部分是不能超出长度的,小数部分是可以超出长度(系统会自动四舍五入)。
  1. create table if not exists my_float1(    money float(11,5)    )charset utf8;
复制代码
  1. mysql>show columns from my_float1;
复制代码
  1. mysql>insert into my_float1 values (123456.12345); -- 小数点为5位小数(有效数据)
复制代码
money:123456.12500

  1. insert into my_float1 values(999999.99);
  2. money:1000000.00000
复制代码


定点型:      小数点固定,精度固定,不会丢失精度。用 decimal 数据类型定义。
      定点型会绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度,在自己的范围内不会进位,不在自己范围内会进位)。
  插入数据时,定点数的整数部分一定不能超出长度(进位)不可以,小数部分的长度是可以随意超出指定长度的(系统会自动四舍五入)。
      decimal(m,d) M 代表总长度,D代表小数部分长度。整数部分长度为 M-D。
  1. create table if not exists my_decimal( money decimal(10,5)     )charset utf8;
复制代码
  1. insert into my_decimal values(66666.99999); -- 整数部分不会进位(有效数据)
  2. money:66666.99999
复制代码






字符串类型。
        在Sql中,字符串类型分为6类,分别是:char、varchar、text、blob、enum和set。
        a)、定长字符串 char。
    char:磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度。
    char(L):L代length(长度)的意思,单位为字符,最大长度值为255。
    char(4):在utf8环境下,需要 4 * 3 = 12个字节。
  b)、变长字符串 varchar。
    varchar 在分配空间的时候,按照最大的空间分配,但是实际上用了多少,是根据具体的情况来确定的。
    varchar(L):L表示length(长度),理论长度是65536个字符,但是会多出 1 到 2 个字节来确定存储的实际长度,但是实际上如果长度超过255,既不用定长,也不用变长,使用文本字符串text。
    varchar(10) :存了10个汉字,在utf8环境下,需要 (10 * 3) + 1 = 31(bytes)来存储。
    定长与变长的实际存储空间(utf8环境下)

      c)、文本字符串。
    如果数据量非常大,通常说超过255个字符就会使用字符串。
    文本字符串根据存储的数据格式进行分类,text和blob。
    text:存储文字(二进制实际上都是存储路径)。
    blob:存储二进制数据(通常不用)。
  d)、枚举字符串。
    枚举:enum,事先将所有可能出现的结果都设计好,实际存储的数据必须是定义好的数据中的其中一个。
            枚举的作用:
        规范数据格式,数据只能是规定的数据的其中之一。
        节省存储空间(枚举通常有1个别名,单选框),枚举实际存储的是字符串本身。
    语法: enum(可能出现的元素列表)。如 enum('男','女');
  1. mysql>create table if not exists my_enum(    sex enum('男','女','保密')    )charset utf8;
  2. mysql>show columns from my_enum;
  3. type:enum('男','女','保密')  
复制代码
  1. 插入数据:insert into my_enum values('男');
  2. 插入数据:insert into my_enum values(1);
复制代码

       d)、集合字符串。
              集合和枚举很类似,实际存储的是数值,而不是字符集(集合是多选)。
      语法: 字段名  set(元素列表)
  1. create table my_set(  hobby set('足球','篮球','乒乓球')   )charset utf8;
  2. show columns from my_set;
  3. type:set('足球','篮球','乒乓球')
复制代码
  1. insert into my_set values(1);
  2. insert into my_set values(3);
复制代码

3、MySql记录长度。         MySql中规定,任何一条记录最长不能超过65535个字节(varchar永远达不到理论值)。
       Varchar的实际长度能达到多少呢?
                     utf8环境下(3个字节存一个汉字),varchar的实际顶配为21844个字符。
       gbk环境下(2个字节存1个汉字),varchar的实际顶配为32766字符
  1. create table my_utf8(      name varchar(65534)     )charset utf8;
  2. error: max =21845  name varchar(21845)
复制代码










回复

使用道具 举报

149

主题

201

帖子

643

积分

版主

Rank: 7Rank: 7Rank: 7

积分
643
 楼主| 发表于 2020-5-10 23:53:54 | 显示全部楼层
本帖最后由 牛大舅 于 2020-5-12 10:57 编辑
  1. mysql> create table if not exists myhome10(score decimal(50,10))charset utf8;
复制代码
  1. mysql> insert into myhome10 values(1234567891234567891234567891234567891234.123456789);
  2. Query OK, 1 row affected (0.00 sec)

  3. mysql> insert into myhome10 values(1234567891234567891234567891234567891234.1234567890);
  4. Query OK, 1 row affected (0.00 sec)

  5. mysql> insert into myhome10 values(1234567891234567891234567891234567891234.12345678901);
  6. Query OK, 1 row affected, 1 warning (0.00 sec)

  7. mysql> insert into myhome10 values(1234567891234567891234567891234567891234.123456789012);
  8. Query OK, 1 row affected, 1 warning (0.00 sec)

  9. mysql> insert into myhome10 values(1234567891234567891234567891234567891234.1234567890123456789123456789);
  10. Query OK, 1 row affected, 1 warning (0.00 sec)

  11. mysql> select * from myhome10
  12.     -> ;
  13. +-----------------------------------------------------+
  14. | score                                               |
  15. +-----------------------------------------------------+
  16. |              123456789123456789123456789.1234567890 |
  17. |    1234567891234567891234567891234567890.0000000000 |
  18. | 1234567891234567891234567891234567891234.0000000000 |
  19. | 1234567891234567891234567891234567891234.1234567890 |
  20. | 1234567891234567891234567891234567891234.1234567890 |
  21. | 1234567891234567891234567891234567891234.1234567890 |
  22. | 1234567891234567891234567891234567891234.1234567890 |
  23. | 1234567891234567891234567891234567891234.1234567890 |
  24. +-----------------------------------------------------+
  25. 8 rows in set (0.00 sec)
复制代码


mysql> create table if not exists myhome10(score decimal(50,10))charset utf8;


zerofill

auto_increment  =0
NOT NULL

UNSIGNED
PRIMARY KEY
COMMENT '编号'
DEFAULT 0





回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|小黑屋|牛大大的个人博客 ( 苏ICP备17043571号-2 )

GMT+8, 2021-6-15 11:00 , Processed in 0.210166 second(s), 29 queries .

Powered by Discuz! X3.4 Licensed

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表