英文网站怎么推广,电子商务网站开发进什么科目,农业建设项目管理信息系统网站,网站建设的前期开发MySQL 作为一个流行的关系型数据库管理系统#xff0c;支持多种数据类型以满足不同的数据处理和存储需求。正确理解和使用这些数据类型对于提高数据库性能、确保数据完整性和准确性至关重要。
MySQL 数据类型
数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。…MySQL 作为一个流行的关系型数据库管理系统支持多种数据类型以满足不同的数据处理和存储需求。正确理解和使用这些数据类型对于提高数据库性能、确保数据完整性和准确性至关重要。
MySQL 数据类型
数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。
数据库中的每个列都应该有适当的数据类型用于限制或允许该列中存储的数据。例如列中存储的为数字则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能所以在设计表时应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事这样做可能会导致数据丢失。因此在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 5 种分别是
整数类型小数类型 (浮点数类型、定点数类型)日期和时间类型文本字符串类型二进制类二进制字符串)
定义字段的数据类型对数据库的优化是十分重要的。
数值类型
整数类型和浮点数类型可以统称为数值数据类型。
整数类型
整数类型又称数值型数据数值型数据类型主要用来存储数字。
MySQL提供了多种数值型数据类型不同的数据类型提供不同的取值范围可以存储的值范围越大所需的存储空间也会越大。
MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT其属性字段可以添加 AUTO_INCREMENT 自增约束条件。
类型名称范围(有符号)范围(无符号)内存占据TINYINT-128〜127(很小的整数)0 〜2551 bytesSMALLINT-32768〜32767(小的整数)0〜655352 bytesMEDIUMINT-8388608〜8388607(中等大小的整数)0〜167772153 bytesINT(INTEGER)-2147483648〜2147483647(普通大小的整数)0〜42949672954 bytesBIGINT-92233720368547758〜9223372036854775807(大整数)0〜184467440737095516158 bytes 显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数数值的位数小于指定的宽度时会由空格填充。 如果插入了大于显示宽度的值只要该值不超过该类型整数的取值范围数值依然可以插入而且能够显示出来。 例如year 字段插入 19999当使用 SELECT 查询该列值的时候MySQL 显示的将是完整的带有 5 位数字的 19999而不是 4 位数字的值。 小数类型
Mysql 中使用 浮点数 和 定点数 来表示小数。
浮点类型有两种分别是单精度浮点数FLOAT和双精度浮点数DOUBLE
定点类型只有一种就是 DECIMAL。
浮点类型和定点类型都可以用 (M, D) 来表示其中 M 称为 精度表示总共的位数(显示宽度)D称为 标度表示小数的位数。
浮点数类型的取值范围为 M1255 和 D130且不能大于 M-2分别表示显示宽度和小数位数。
M 和 D 在 FLOAT 和 DOUBLE 中是可选的FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。
DECIMAL 的默认 D 值为 0、M 值为 10。
类型名称说明存储需求FLOAT单精度浮点数4 bytesDOUBLE双精度浮点数8 bytesDECIMAL(M, D), DEC压缩的“严格”定点数M2 bytes
FLOAT 类型的取值范围如下 有符号的取值范围-3.402823466E38-1.175494351E-38。 无符号的取值范围0、 -1.175494351E-38 -3.402823466E38。
DOUBLE 类型的取值范围如下 有符号的取值范围-1.7976931348623157E308-2.2250738585072014E-308。 无符号的取值范围0 和 -2.2250738585072014E-308-1.7976931348623157E308。
DECIMAL 可能的最大取值范围与 DOUBLE 相同但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D则取值范围将随 M 的变大而变大。 不论是定点还是浮点类型如果用户指定的精度超出精度范围则会四舍五入进行处理。 FLOAT 和 DOUBLE 在不指定精度时默认会按照实际的精度由计算机硬件和操作系统决定DECIMAL 如果不指定精度默认为100。
浮点数相对于定点数的优点是在长度一定的情况下浮点数能够表示更大的范围缺点是会引起精度问题。
在对精度要求比较高的时候如货币、科学数据使用 DECIMAL 的类型比较好另外两个浮点数进行减法和比较运算时也容易出问题所以在使用浮点数时需要注意并尽量避免做浮点数比较。
日期和时间类型
在MySQL中有多处表示日期的数据类型YEAR、TIME、DATE、DTAETIME、TIMESTAMP。
每一个类型都有合法的取值范围当指定不合法的MySQL无法表示的值时系统将“零”值插入数据库中
类型范围格式用途存储YEAR1901~2155YYYY年份值1 bytesDATE1000-01-01~9999-12-31YYYY-MM-DD日期值3 bytesTIME‘-838:59:59’~‘838:59:59’HH:MM:SS时间值或持续时间3 bytesDATETIME‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’YYYY-MM-DD hh:mm:ss混合日期和时间值8 bytesTIMESTAMP‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTCYYYY-MM-DD hh:mm:ss混合日期和时间值时间戳4 bytes
YEAR类型
YEAR 类型是一个单字节类型用于表示年在存储时只需要 1 个字节。可以使用各种格式指定 YEAR如下所示 以 4 位字符串或者 4 位数字格式表示的 YEAR范围为 ‘1901’’2155’。输入格式为 ‘YYYY’ 或者 YYYY例如输入 ‘2010’ 或 2010插入数据库的值均为 2010。 以 2 位字符串格式表示的 YEAR范围为 ‘00’ 到 ‘99’。‘00’’69’ 和 ‘70’’99’ 范围的值分别被转换为 20002069 和 19701999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000 以 2 位数字表示的 YEAR范围为 199。199 和 7099 范围的值分别被转换为 20012069 和 19701999 范围的 YEAR 值。注意在这里 0 值将被转换为 0000而不是 2000。 两位整数范围与两位字符串范围稍有不同。例如插入 3000 年读者可能会使用数字格式的 0 表示 YEAR实际上插入数据库的值为 0000而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’才可以被正确解释为 3000非法 YEAR值将被转换为 0000。 TIME 类型
TIME 类型用于只需要时间信息的值在存储时需要 3 个字节。格式为 **HH:MM:SS。HH 表示小时MM 表示分钟SS 表示秒 。
TIME 类型的取值范围为 -83859598385959小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间必须小于 24 小时还可能是某个事件过去的时间或两个事件之间的时间间隔可大于 24 小时或者甚至为负。
可以使用各种格式指定 TIME 值如下所示。 D HH:MM:SS 格式的字符串。还可以使用这些“非严格”的语法 HH:MM:SS、HH:MM、D HH 或 SS。这里的 D 表示日可以取 034 之间的值。在插入数据库时D 被转换为小时保存格式为 “Dx24HH” HHMMSS 格式、没有间隔符的字符串或者 HHMMSS 数字格式的数值假定是有意义的时间。例如‘101112’ 被理解为’101112’但是 ‘106112’ 是 不合法的它有一个没有意义的分钟部分在存储时将变为 000000。 为 TIME 列分配简写值时应注意如果没有冒号MySQL 解释值时假定最右边的两位表示秒。MySQL 解释 TIME 值为过去的时间而不是当前的时间。 例如读者可能认为 ‘1112’ 和 1112 表示 111200即 11 点过 12 分钟但MySQL 将它们解释为 001112即 11 分 12 秒。同样 ‘12’ 和 12 被解释为000012。相反TIME 值中如果使用冒号则肯定被看作当天的时间也就是说‘1112’ 表示 111200而不是 001112 DATE类型
DATE 类型用于仅需要日期值时没有时间部分在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’其中 YYYY 表示年MM 表示月DD 表示日。
在给 DATE 类型的字段赋值时可以使用字符串类型或者数字类型的数据插入只要符合 DATE 的日期格式即可。 以 YYYY-MM-DD 或者 YYYYMMDD 字符串格式表示的日期取值范围为 ‘1000-01-01’’9999-12-3’。例如输入 ‘2015-12-31’ 或者 ‘20151231’插入数据库的日期为2015-12-31。 以 YY-MM-DD 或者 YYMMDD字符串格式表示日期在这里YY表示两位的年值。MySQL 解释两位年值的规则‘0069’ 范围的年值转换为 ‘20002069’**7099’ 范围的年值转换为 ‘19701999’。 例如输入 ‘15-12-31’插入数据库的日期为 2015-12-31 输入 ‘991231’插入数据库的日期为 1999-12-31。 以 YYMMDD 数字格式表示的日期与前面相似00~69 范围的年值转换为 200020697099 范围的年值转换为 19701999。 例如输入 151231插入数据库的日期为 2015-12-31输入 991231插入数据库的日期为 1999-12-31。 使用 CURRENT_DATE 或者 NOW()插入当前系统日期。 MySQL 允许“不严格”语法任何标点符号都可以用作日期部分之间的间隔符例如‘98-11-31’、‘98.11.31’、‘98/11/31’和’981131’ 是等价的这些值也可以正确地插入数据库。 DATETIME 类型
DATETIME 类型用于需要同时包含日期和时间信息的值在存储时需要 8 个字节。日期格式为 YYYY-MM-DD HH:MM:SS其中 YYYY 表示年MM 表示月DD 表示日HH 表示小时MM 表示分钟SS 表示秒。
在给 DATETIME 类型的字段赋值时可以使用字符串类型或者数字类型的数据插入只要符合 DATETIME 的日期格式即可如下所示。 以 YYYY-MM-DD HHMMSS 或者 YYYY-MM-DD HH:MM:SS 字符串格式表示的日期取值范围为 ‘1000-01-01 000000’’9999-12-3 235959’。 例如输入 ‘2014-12-31 050505’ 或者 20141231050505’插入数据库的 DATETIME 值都为 2014-12-31 050505。 以 YY-MM-DD HH:MM:SS 或者 YY-MM-DD- HH:MM:SS 字符串格式表示的日期在这里 YY 表示两位的年值。与前面相同‘0079’ 范围的年值转换为 ‘20002079’‘8099’ 范围的年值转换为 ‘19801999’。 例如输入 ‘14-12-31 050505’插入数据库的 DATETIME 为 2014-12-31 050505输入 141231050505插入数据库的 DATETIME 为 2014-12-31 050505。 以 YYYY-MM-DD HH:MM:SS 或者 YY-MM-DD- HH:MM:SS 数字格式表示的日期和时间。 例如输入 20141231050505插入数据库的 DATETIME 为 2014-12-31 05:05:05输入 140505050505插入数据库的 DATETIME 为 2014-12-31 05:05:05。 MySQL 允许“不严格”语法任何标点符号都可用作日期部分或时间部分之间的间隔符。 例如‘98-12-31 11:30:45’、‘98.12.31 113035’、‘98/12/31 11%30%45’ 和 ‘981231 113045’ 是等价的这些值都可以正确地插入数据库。 TIMESTAMP类型
TIMESTAMP 的显示格式与 DATETIME 相同显示宽度固定在 19 个字符日期格式为 YYYY-MM-DD HHMMSS在存储时需要 4 个字节。
但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围为 ‘1970-01-01 000001’ UTC ‘2038-01-19 031407’ UTC 。在插入数据时要保证在合法的取值范围内。 协调世界时英Coordinated Universal Time法Temps Universel Coordonné又称为世界统一时间、世界标准时间、国际协调时间。英文CUT和法文TUC的缩写不同作为妥协简称 UTC。 TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外还有一个最大的区别是 DATETIME 在存储日期数据时按实际输入的格式存储即输入什么就存储什么与时区无关 而 TIMESTAMP 值的存储是以 UTC世界标准时间格式保存的存储时对当前时区进行转换检索时再转换回当前时区。即查询时根据当前时区的不同显示的时间值是不同的。 如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值结果值的时间部分被设置为 ‘000000’因此 DATE 值未包含时间信息。 如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值结果值的时间部分被删除因此DATE 值未包含时间信息。 文本字符串类型
字符串类型用来存储文本字符串数据还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较还可以进行正则表达式的匹配查找。
存储和检索大型文本数据可能会对数据库的性能和资源消耗产生影响。在处理文本数据时需要合理使用和管理存储空间以及通过适当的方式处理和优化查询操作。
MySQL中的文本字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
M 可以为其指定字节
类型名称说明存储需求CHAR(M)固定长度非二进制字符串M 字节1M255VARCHAR(M)变长非二进制字符串L1字节在此L M和 1M255TINYTEXT非常小的非二进制字符串L1字节在此L2^8TEXT小的非二进制字符串L2字节在此L2^16MEDIUMTEXT中等大小的非二进制字符串L3字节在此L2^24LONGTEXT大的非二进制字符串L4字节在此L2^32ENUM枚举类型只能有一个枚举字符串值1或2个字节取决于枚举值的数目 (最大值为65535)SET一个设置字符串对象可以有零个或 多个SET成员1、2、3、4或8个字节取决于集合 成员的数量最多64个成员
VARCHAR和 TEXT 类型是变长类型其存储需求取决于列值的实际长度在前面的表格中用 L 表示而不是取决于类型的最大可能尺寸。 例如一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”L 是 4而存储要求 5 个字节。 CHAR和VARCHAR类型
CHAR(M) 为固定长度字符串在定义时指定字符串列长。当保存时在右侧填充空格以达到指定的长度。M 表示列的长度范围是 0255 个字符。 例如CHAR(4) 定义了一个固定长度的字符串列包含的字符个数最大为 4。无论存入数据的长度为多少所占用的空间均为4字节。当检索到 CHAR 值时尾部的空格将被删除。 VARCHAR(M) 是长度可变的字符串M 表示最大列的长度M 的范围是 065535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定而实际占用的空间为字符串的实际长度加 1。 例如VARCHAR(50) 定义了一个最大长度为 50 的字符串如果插入的字符串只有 10 个字符则实际存储的字符串为 10 个字符和一个字符串结束字符此时占用的空间为 11 字节如果插入的字符串只有 4 个字符那么此时占用的空间为 5 字节。 VARCHAR 在值保存和检索时尾部的空格仍保留。 TEXT 类型
TEXT 列保存非二进制字符串如文章内容、评论等。当保存或查询 TEXT 列的值时不删除尾部空格。
TEXT 类型分为 4 种TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。 TINYTEXT 表示长度为 25528-1字符的 TEXT 列。 TEXT 表示长度为 65535216-1字符的 TEXT 列。 MEDIUMTEXT 表示长度为 16777215224-1字符的 TEXT 列。 LONGTEXT 表示长度为 4294967295 或 4GB232-1字符的 TEXT 列。
ENUM类型
ENUM 是一种用于存储枚举值的数据类型。枚举值指的是一组有限的可能选项可以在列定义时指定这些选项并且每个列只能存储这些指定的枚举值之一。如果创建的成员中有空格尾部的空格将自动被删除。
ENUM 类型的字段在取值时能在指定的枚举列表中获取而且一次只能取一个。
枚举值为表创建时列规定中枚举的一列值。其语法格式如下
字段名 ENUM( 值1, 值1, …, 值n )
# 字段名指将要定义的字段值 n 指枚举列表中第 n 个值。CREATE TABLE example (status ENUM(active, inactive, pending)
);向 ENUM 列插入数据时只能为列赋予定义的枚举值之一。例如
INSERT INTO example (status) VALUES (active);
INSERT INTO example (status) VALUES (pending);# 查询包含ENUM列的数据时返回的结果将显示存储的枚举值如果尝试插入一个不在定义的枚举选项内的值将会引发错误。
ENUM 值在内部用整数表示每个枚举值均有一个索引值列表值所允许的成员值从 1 开始编号MySQL 存储的就是这个索引编号枚举最多可以有 65535 个元素。 例如定义 ENUM 类型的列‘first’‘second’‘third’: 值索引Nullnull“”0first1second2third3
ENUM 值依照列索引顺序排列并且空字符串排在非空字符串前NULL 值排在其他所有枚举值前。 ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULLNULL 值则为该列的一个有效值并且默认值为 NULL。 如果 ENUM 列被声明为 NOT NULL其默认值为允许的值列表的第 1 个元素。 ENUM 列虽然提供了一种存储固定选项的方式但它也有一些限制。添加新的枚举选项或者重新排序现有的选项需要执行ALTER TABLE语句这可能会引起某些操作的复杂性。
SET类型
SET 是一种用于存储一组可选值的数据类型。允许从一个预定义的值集合中选择零个或多个值并将其作为一组位标志存储在列中SET 列 最多可以有 64 个成员值为表创建时规定的一列值。
语法格式如下:
SET( 值1, 值2, …, 值n )CREATE TABLE example (colors SET(red, green, blue)
);使用 INSERT 或 UPDATE 语句向 SET 列插入数据时与 ENUM 列不同的是 可以选择一个或多个列选项。多个选项应以逗号分隔。例如
INSERT INTO example (colors) VALUES (red);
INSERT INTO example (colors) VALUES (red,green);# 查询包含SET列的数据时返回的结果将显示存储的选项
# 还可以使用LIKE运算符进行SET列的模糊匹配查询与 ENUM 类型相同SET 值在内部用整数表示列表中每个值都有一个索引编号。当创建表时SET 成员值的尾部空格将自动删除。 如果插入 SET 字段中的列值有重复则 MySQL 自动删除重复的值插入 SET 字段的值的顺序并不重要MySQL 会在存入数据库时按照定义的顺序显示如果插入了不正确的值默认情况下MySQL 将忽视这些值给出警告。 SET 类型有一些限制例如SET列在存储和检索数据时会有一些额外的复杂性并且在定义列后更改选项集可能会使用 ALTER TABLE 语句来进行更改。
二进制类
二进制类型可以用于存储各种二进制数据例如图像、音频、视频、文档等
MySQL 中的二进制字符串有时也被直接成为二进制类型有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
m 表示可以为其指定长度
类型名称说明存储需求BIT(M)位字段类型大约 (M7)/8 字节BINARY(M)固定长度二进制字符串M 字节VARBINARY (M)可变长度二进制字符串M1 字节TINYBLOB (M)非常小的BLOBL1 字节在此L2^8BLOB (M)小 BLOBL2 字节在此L2^16MEDIUMBLOB (M)中等大小的BLOBL3 字节在此L2^24LONGBLOB (M)非常大的BLOBL4 字节在此L2^32
BIT 类型
BIT 是一种用于存储位(bit)值的数据类型。BIT 类型可以存储二进制位的序列其中每个位可以是0或1。
当查询包含BIT列的数据时返回的结果将显示存储的位(bit)值。
BIT 类型有两种形式固定长度的BIT(M) 和 可变长度的BIT。
固定长度的BIT(M)表示具有固定长度 M 的位(bit)序列。在创建表时需要指定位序列的长度 M 范围可以是 1~64 。
# 创建一个固定长度为8的位BIT列
CREATE TABLE example (flags BIT(8)
);如果为 BIT(M) 列分配的值的长度小于 M 位在值的左边用 0 填充。例如为 BIT(6) 列分配一个值 b’101’其效果与分配 b’000101’ 相同。
变长度的BIT表示可以存储不定长度位(bit)序列的数据类型。这意味着可以根据需要存储不同长度的位(bit)序列。
# 创建一个可变长度的位列
CREATE TABLE example (data BIT
);BIT 数据类型用来保存位字段值 例如: 以二进制的形式保存数据 1313 的二进制形式为 1101在这里需要位数至少为 4 位的 BIT 类型即可以定义列类型为 BIT(4)。 大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。 默认情况下MySQL 不可以插入超出该列允许范围的值因而插入数据时要确保插入的值在指定的范围内。 需要注意的是BIT类型主要用于存储小的位(bit)序列例如存储布尔值或使用位掩码表示多个选项的情况。对于存储较大的位序列可能需要考虑使用BLOB或其他适当的数据类型。
BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR不同的是它们包含二进制字节字符串。
语法格式如下
列名称 BINARY(M) 或者 VARBINARY(M)BINARY类型用于存储固定长度的二进制数据。在创建表时需要指定BINARY列的长度范围可以是 1~255。 指定长度后不足最大长度的将在它们右边填充 “\0” 补齐以达到指定长度。 VARBINARY类型用于存储可变长度的二进制数据。VARBINARY列可以存储不同长度的二进制数据根据实际需要分配所需的空间,实际占用的空间字符串的实际长度1。
CREATE TABLE example(data BINARY(3)
);INSERT INTO example (data) VALUES (a);
# 插入后的列值将会是01100001 00000000 总共3个字节前面是字符 ‘a’ 的二进制表示后面被填充了一个字节的0CREATE TABLE example(data VARBINARY(255)
);BLOB 类型
BLOBBinary Large Object) 是一种在数据库中存储 大量 二进制数据的数据类型。在MySQL中BLOB用于存储 大型 的 可变数量 的非结构化二进制数据例如图像、音频、视频、文档等。
数据类型存储范围TINY BLOB最大长度为255 (28-1) bytesBLOB最大长度为65535 (216-1) bytesMEDIUM BLOB最大长度为16777215 (224-1) bytesLONG BLOB最大长度为4294967295或4GB (231-1) bytes
BLOB列存储的是二进制字符串字节字符串。BLOB 列是字符集且排序和比较基于列值字节的数值
TEXT 列存储的是非进制字符串字符字符串。TEXT 列有一个字符集且根据字符集对值进行排序和比较。
需要注意的是存储和检索大型BLOB数据可能会对数据库的性能和资源消耗产生影响。在处理 BLOB 类型数据时通常建议合理使用和管理存储空间以及通过适当的方式处理和优化查询操作。
特殊类型
在数据库中NULL 是一个特殊的值表示缺失或未知的数据。它表示某个字段或列没有具体的值。
以下是关于 NULL 的一些重要事项 NULL 不等于任何值包括它自己。所以NULL NULL 的比较结果是 NULL而不是 true 或 false。 在条件比较中可以使用 IS NULL 或 IS NOT NULL 来判断字段或列是否为 NULL。 NULL 值可以在数据库表中出现它表示该字段在特定行中没有具体的值。 NULL 不是空字符串它表示一个缺失的值而空字符串是一个有效的字符串。 在进行各种计算和操作时与 NULL 相关的结果通常为 NULL。
对于包含 NULL 值的列需要特别小心处理确保查询和计算不产生意外的结果。可以使用 IFNULL() 或 COALESCE() 函数来处理 NULL 值提供默认值或进行转换。
MySQL 数据类型的选择
MySQL 提供了大量的数据类型为了优化存储和提高数据库性能在任何情况下都应该使用最精确的数据类型。
字符串类型是通用的数据类型任何内容都可以保存在字符串中数字和日期都可以表示成字符串形式。
但是也不能把所有的列都定义为字符串类型。对于数值类型如果把它们设置为字符串类型的会使用很多的空间。用字符串类型存储数字相较于使用数值类型存储而言效率较为低下。
另外需要注意的是由于对数字和字符串的处理方式不同查询结果也会存在差异。
例如对数字的排序与对字符串的排序是不一样的 数字 2 小于数字 11但字符串 ‘2’ 却比字符串 ‘11’ 大。此问题可以通过把列放到数字上下文中来解决如下面 SQL 语句 SELECT course 0 as num ... ORDER BY num;让 course 列加上 0可以强制列按数字的方式来排序但这么做很明显是不合理的。
如果让 MySQL 把一个字符串列当作一个数字列来对待会迫使让列里的每一个值都执行从字符串到数字的转换操作效率低。
而且在计算过程中使用这样的列会导致 MySQL 不会使用这些列上的任何索引从而进一步降低查询的速度。
在选择数据类型时首先要考虑这个列存放的值是什么类型的。一般来说用 数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间
数值类型
如果要存储的数字是整数没有小数部分则使用[[数值类型#整数类型|整数类型]]
如果列的取值范围是 1~99999 之间的整数则UNSIGNED MEDIUMINT类型是最好的选择
CREATE TABLE exmple(num UNSIGNED MEDIUMINT
);根据实际所需取值范围来选择适用的整数类型选取合适的存储来提升效率
如果要存储的数字是[[数值类型#小数类型|小数类型]]带有小数部分则可以选用DECIMAL或浮点类型但是一般选择FLOAT类型浮点类型的一种。
正确选择数据类型不仅可以使表的存储空间变小也会提高性能。因为与较长的列相比较短的列的处理速度更快。当读取较短的值时所需的磁盘读写操作会更少并且可以把更多的键值放入内存索引缓冲区里。
如果无法获知各种可能值的范围,将来还可以使用ALTER TABLE让该列变得更大些。
实际问题
如果数值类型需要存储的数据为货币如人民币。在计算时使用到的值常带有元和分两个部分。它们看起来像是浮点值但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题。
把货币表示成 DECIMAL(M,2) 类型其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差计算是精确的。
对于电话号码、信用卡号和社会保险号都会使用非数字字符。由于空格和短划线不能直接存储到数字类型列里除非去掉其中的非数字字符。但即使去掉了其中的非数字字符也不能把它们存储成数值类型以避免丢失开头的“零”。
日期和时间类型
如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。存储较大的日期最好使用 DATETIME。
默认情况下当插入一条记录但并没有指定 TIMESTAMP 这个列值时MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时使用 TIMESTAMP 是方便的另外 TIMESTAMP 在空间上比 DATETIME 更有效。
MySQL 没有提供时间部分为可选的日期类型。如果时间部分是可选的那么可以使用 DATE 列来记录日期再用一个单独的 TIME 列来记录时间。然后设置 TIME 列可以为 NULL。
CREATE TABLE example(date DATE NOT NULL, # 日期必需time TIME NULL # 时间可选可为NULL
);字符串类型 如果某个字符串列用于表示某种固定集合的值那么可以考虑使用数据类型。ENUM(多个值中选取一个时) 或 SET需要选取多个值时。 CHAR 是固定长度所以它的处理速度比 VARCHAR 的速度要快但是它的缺点就是浪费存储空间。所以对存储不大但在速度上有要求的可以使用 CHAR 类型反之可以使用 VARCHAR类型来实现。 对于 MyISAM 存储引擎最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化从而使数据检索更快用空间换时间。 对于 InnoDB 存储引擎最好使用可变长度的数据列因为 InnoDB 数据表的存储格式不分固定长度和可变长度因此使用 CHAR 不一定比使用 VARCHAR 更好但由于 VARCHAR 是按照实际的长度存储比较节省空间所以对磁盘 I/O 和数据存储总量比较好。
二进制类型
BLOB 是二进制字符串TEXT 是非二进制字符串两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等而TEXT 只能存储纯文本文件。
总结
正确选择和使用 MySQL 中的数据类型对于提高数据库性能、确保数据准确性和完整性至关重要。在选择数据类型时需要根据业务需求和数据特性来决定避免使用过于复杂或不必要的数据类型。同时了解每种数据类型的优缺点和使用场景可以帮助我们更好地设计和优化数据库结构。
