进学阁

业精于勤荒于嬉，行成于思毁于随

0%

MySQL数据类型

发表于 更新于 分类于

Mysql提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。接下来以Mysql5.0为例，详细介绍Mysql中的各种数据类型。

数值类型

整数类型
字节 最小值 最大值 备注
TINYINT 1 有符号-128(-27) 无符号0 有符号127(27-1) 无符号255(28-1) 很小的整数
SMALLINT 2 有符号-32768(-215) 无符号0 有符号32767(215-1) 无符号65535(216) 小的整数
MEDIUMINT 3 有符号- 8388608(-223) 无符号0 有符号8388607(223-1) 无符号1677215(224-1) 中等大小的整数
INT、INTEGER 4 有符号- 2147483648(-231) 无符号0 有符号2147483647(231-1) 无符号4294967295(232-1) 普通大小的整数
BIGINT 8 有符号-9223372036854775808(-263) 无符号0 有符号9223372036854775807(263-1) 无符号18446744073709551615(264-1) 大的整数

对于整型数据，Mysql支持在类型名称后面的小括号内指定显示宽度。例如int(5)表示当数值宽度小于5位的时候在数字前面，默认用空格填满宽度，如果不显示指定宽度则默认为int(11)。

:::tips
如果需要用哪个数字’0’填充，则需要使用zerofill，并且使用zerofill 时，默认会自动unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128+127，无符号为0256。

:::

浮点数类型
字节 最小值 最大值 备注
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38 单精度浮点型，m总个数，d小数位
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308 双精度浮点型， m总个数，d小数位
DEC(M,D) M+2 最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定 压缩严格的定点数
DECIMAL(M,D)

 对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示金额等精度高的数据。

  浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M位数字（整数位+小数位），其中D位于小数点后面。M和D又称为精度和标度。

:::tips
float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

decimal插入的数据超过精度之后会触发警告

decimal采用的是四舍五入。
float和double采用的是四舍六入五成双。

四舍六入五成双：就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位；如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉。

如果将数据库中 float 、 double类型的数据进行计算时，会存在精度问题。而使用decimal时是正常的，所以非int数据，一般decimal用的比较多

:::

日期时间类型
字节 最小值 最大值 备注
DATE 4 1000-01-01 9999-12-31 YYYY-MM-DD
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
TIMESTAMP 4 19700101080001 2038-01-19 03:14:07UTC（格林尼治时间） YYYY-MM-DD HH:MM:SS
TIME 3 -838:59:59 838:59:59 HH:MM:SS
YEAR 1 1901 2155 YYYY

如果要用来表示年月日，通常用DATE来表示。

  如果要用来表示年月日时分秒，通常用DATETIME表示。

  如果只用来表示时分秒，通常用TIME来表示。

  如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。

  如果只是表示年份，可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在4位格式中，允许的值是1901～2155和0000。在2位格式中，允许的值是70～69，表示从1970～2069年。

  DATETIME是DATE和TIME的组合。

:::tips
TIMESTAMP还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的。

:::

尽量使用timestamp，空间效率高于datetime（datetime占用8字节，timestamp占用4字节）。

  如果需要存储微秒，可以使用bigint存储。

  DATETIME表示的时间范围较大，TIMESTAMP表示的时间范围较小。

  关于表中的更新时间字段，使用示例（插入数据时为当前时间，更新数据时也会随之更新）：

字符串类型
字节 范围 备注
char(M) m [0,m]，m的范围[0, 28-1] 定长字符串
varchar(M) m [0,m]，m的范围[0, 216-1] 0-65535 字节
tinyblob L+1 0-255(28 -1)字节 不超过 255 个字符的二进制字符串
blob L+2 0-65535( 216-1)字节 二进制形式的长文本数据
mediumblob L+3 0-16777215(224 -1)字节 二进制形式的中等长度文本数据
longblob L+4 0-4294967295( 232-1)字节 二进制形式的极大文本数据
tinytext L+1 0-255( 28-1)字节 短文本字符串
text L+2 0-65535(216 -1)字节 长文本数据
mediumtext L+3 0-16777215(224 -1)字节 中等长度文本数据
longtext L+4 0-4294967295( 232-1)字节 极大文本数据

char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。

表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。
 CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：

CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值，存储的字符串长度不足时用空格填充到特定长度；

而VARCHAR列中的值为可变长字符串，也就是说申请的只是最大长度，占用的空间为实际字符长度+1，最后一个字符存储使用了多长的空间。
 在检索效率上来讲,char > varchar。

在检索的时候，CHAR删除了尾部的空格，而VARCHAR则保留这些空格

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

  关于varchar(n)中n的单位，Mysql4.1及之后的版本，VARCHAR的单位是字符；Mysql4.1之前的版本，VARCHAR的单位是字节。

  varchar和char的使用策略：

:::tips

  1. 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
  2. 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
  3. 使用时要注意只分配需要的空间。
  4. 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

:::

TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT也称作 非标准字符串类型。

Mysql数据类型相关问题

若一张表中只有一个字段VARCHAR(N) 类型，utf8编码，则N最大值为多少

  由于utf8的每个字符最多占用3个字节。而MySQL定义行的长度不能超过65535，因此N的最大值计算方法为：(65535-1-2)/3。

 减去1的原因是实际存储从第二个字节开始，减去2的原因是因为要在列表长度存储实际的字符长度，除以3是因为utf8限制：每个字符最多占用3个字节。

选择合适的数据类型
CHAR与VARCHAR

  CHAR和VARCHAR类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR属于固定长度的字符类型，而VARCHAR属于可变长度的字符类型。

  看一个字符串值保存到CHAR(4)和VARCHAR(4)列的结果对比：

  由于CHAR是固定长度的，所以它的处理速度比VARCHAR快得多，但是缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。

  在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，简单概括：

:::tips
  MyISAM：建议使用固定长度的数据列代替可变长度的数据列。

  MEMORY：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。

  InnoDB：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

:::

浮点数与定点数

 浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL中float、double（或 real）用来表示浮点数。

  定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确的保存数据。

  浮点数和定点数使用的几个原则：

:::tips

  1. 浮点数存在误差问题；
  2. 对货币等对精度敏感的数据，应该用定点数表示或存储；
  3. 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
  4. 要注意浮点数中一些特殊值的处理。

:::

日期类型选择

MySQL提供的常用日期类型有DATE、TIME 、DATETIME、TIMESTAMP。使用原则：

:::tips
根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。

如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，

而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

:::

ENUM和VARCHAR

ENUM类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

  如果有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，这些字段的取值是有限而且固定的，那么，就应该使用ENUM而不是VARCHAR。

数据类型选择的一些建议
选小不选大

  一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。

简单就好

  简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。

尽量避免NULL

  尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。

浮点类型的建议统一选择decimal
记录时间的建议使用int或者bigint类型

将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引

使用int做主键和使用string有什么优劣

使用整数（int）作为主键

优势：

:::tips
性能：整数类型通常占用较少的存储空间，并且在进行比较和排序操作时速度更快。

索引效率：整数类型的索引通常比字符串类型的索引更加紧凑和高效。

自增属性：许多数据库系统支持整数类型的自增属性，这可以自动为新记录分配一个唯一的标识符，无需应用程序干预。

空间效率：整数类型占用的存储空间较小，有助于减少数据库的总体大小。

:::

劣势：

:::tips

  1. 可读性：整数类型不如字符串类型直观，不容易从主键值中直接获取有关记录的信息。
  2. 扩展性：在某些情况下，如果整数主键达到其最大值，可能需要更复杂的方案来处理。

:::

使用字符串（string）作为主键

优势：

:::tips

  1. 可读性：字符串类型的主键通常更容易理解，因为它们可能包含描述性信息。
  2. 灵活性：字符串类型可以容纳更多种类的数据，包括字母、数字和特殊字符，使得它们在处理复杂的主键需求时更加灵活。
  3. 扩展性：字符串类型的主键在达到存储限制之前可以容纳更多的信息。

:::

劣势：

:::tips
性能：字符串类型通常比整数类型占用更多的存储空间，并且在进行比较和排序操作时速度较慢。

索引效率：字符串类型的索引通常比整数类型的索引更大且效率较低。

错误风险：在处理字符串类型的主键时，需要更加小心地处理大小写敏感性、空格、特殊字符等问题，以避免潜在的错误。

存储空间：由于字符串的长度可变，因此可能会浪费存储空间，特别是在存储较短的字符串时。

:::

如何选择合适的主键类型

 1、根据业务需求：根据具体业务场景来选择合适的主键类型。例如，如果业务需求中需要处理大量数值类型的数据，可以使用 int 作为主键；如果需要处理字符串类型的数据，可以使用 string 作为主键。

  2、考虑数据量的大小：如果数据量较大，可以使用 int 作为主键，以提高查询和更新的效率；如果数据量较小，可以使用 string 作为主键，以提高数据的可读性和可操作性。

  3、考虑数据类型的稳定性和一致性：如果数据类型需要保持稳定性和一致性，可以使用 int 作为主键；如果数据类型可能会发生变化，可以使用 string 作为主键。 综上所述，使用 int 和 string 作为主键各有优劣。在实际应用中，需要根据具体业务场景和数据特点来选择合适的主键类型，以满足数据的存储、管理和处理需求。

Mysql字符集

简单地说，字符集就是一套文字符号及其编码、比较规则的集合。
Mysql常用字符集：

字符集 是否定长 编码方式 说明
ACSII 是 单字节 7 位编码 最早的奠基性字符集
GBK 是 双字节编码
UTF-32 是 4 字节编码 目前很少采用
UTF-16 否 2 字节或 4 字节编码 Java和Windows XP/NT等内部使用UTF-16
UTF-8 否 1 至 4 字节编码 互联网和UNIX/Linux 广泛支持的Unicode字符集；MySQLServer也使用UTF-8
utf8mb4 否 4 字节编码 完全支持所有Unicode字符，包括Emoji。建议在新项目中使用此字符集。

选择字符集时，要考虑的因素：

  1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集。对MySQL来说，目前就是UTF-8。
  2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。
  3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK。因为，相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库cache，以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择UTF-8更好，因为GBK、UCS-2、UTF-16的西文字符编码都是2个字节,会造成很大不必要的开销。
  4. 如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。

MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集。

总的来说，建议在能够完全满足应用的前提下，尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接地提高了系统的性能。

  有很多字符集可以保存汉字，比如utf8、gb2312、gbk等等，但是常用的是gb2312和gbk。因为gb2312字库比gbk字库小，有些偏僻字（如：洺）不能保存。因此在选择字符集时一定要权衡这些偏僻字在应用出现的几率以及造成的影响，不能做出肯定答复的最好选用gbk。

  在实际项目开发时，使用utf8mb4即可，即：CHARSET=utf8mb4。