金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 选择合适的数据类型,MySQL中数据类型介绍金沙棋

选择合适的数据类型,MySQL中数据类型介绍金沙棋

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:09

一. char 与varchar比较

char 与 varchar

  1. char 属于固定长度的字符类型,而 varchar 属于可变长度的字符类型

  2. 检索时 char 会删除尾部的空格

  3. 不同存储引擎使用建议

    MyISAM:char

    MEMORY:char

    InnoDB:varchar

数据库设计

前要:字符串长度不是定义字节数,而是字符数,多字节字符集需要更多的空间存储单个字符。

一、MySQL的数据类型

主要包括以下五大类:

整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型:FLOAT、DOUBLE、DECIMAL

字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型:Date、DateTime、TimeStamp、Time、Year

其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

 

1、整型

MySQL数据类型 含义(有符号)
tinyint(m) 1个字节  范围(-128~127)
smallint(m) 2个字节  范围(-32768~32767)
mediumint(m) 3个字节  范围(-8388608~8388607)
int(m) 4个字节  范围(-2147483648~2147483647)
bigint(m) 8个字节  范围(+-9.22*10的18次方)

取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。

 int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。

 

2、浮点型(float和double)

MySQL数据类型 含义
float(m,d) 单精度浮点型    8位精度(4字节)     m总个数,d小数位
double(m,d) 双精度浮点型    16位精度(8字节)    m总个数,d小数位

设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。整数部分最大是3位,如果插入数12.123456,存储的是12.1234,如果插入12.12,存储的是12.1200.

 

3、定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。 

decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。

 

4、字符串(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 固定长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符

char和varchar:

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。 

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),

所以varchar(4),存入3个字符将占用4个字节。 

3.char类型的字符串检索速度要比varchar类型的快。
varchar和text: 

1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字

节。 

2.text类型不能有默认值。 

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

 

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。

2._BLOB存储的数据只能整体读出。 

3._TEXT可以指定字符集,_BLO不用指定字符集。

 

6.日期时间类型

MySQL数据类型 含义
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间

若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

 

数据类型的属性

 

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

 

  金沙棋牌app手机下载 1

text 与 blob

  1. 二者的主要差别是 blob 能用来保存二进制数据,而 text 只能保存字符数据

  2. blob 和 text 值会引起一些性能问题,特别是在执行了大量的删除操作时

    删除操作会在数据表中留下很大的空洞,以后填入这些空洞的记录在插入的性能上会有影响。微了提高性能,建议定期使用 optimize table 功能对这类表进行碎片整理,避免因为空洞导致性能问题

  3. 可以使用合成的(Synthetic)索引来提高大文本字段的查询性能

    合成索引就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的数据列中,接下来就可以通过检索散列值找到数据行了。但是,要注意这种技术只能用于精确匹配的查询。可以使用 md5()sha1(),或 crc32() 函数生成散列值

  4. 在不必要的时候避免检索大型的 blob 或 text 值

  5. 把 blob 或 text 分离到单独的表中

字段设计维度的思考

可以按照以下维度考虑设计:

  • 按照字段的使用频率设计,预判性的分为冷、温、热字段设计表
  • 根据展示数据展示位置来设计,比如有些数据只在个人中心显示而不会在其他地方显示
  • 根据字段的大小设计表,比如,有些大文本 text,blob的类型的可以考虑拆分。
  • 按照读/写(更新)的比例来设计表

二、MYSQL数据类型的长度和范围

各数据类型及字节长度一览表:

数据类型 字节长度 范围或用法
Bit 1 无符号[0,255],有符号[-128,127],天缘博客备注:BIT和BOOL布尔型都占用1字节
TinyInt 1 整数[0,255]
SmallInt 2 无符号[0,65535],有符号[-32768,32767]
MediumInt 3 无符号[0,2^24-1],有符号[-2^23,2^23-1]]
Int 4 无符号[0,2^32-1],有符号[-2^31,2^31-1]
BigInt 8 无符号[0,2^64-1],有符号[-2^63 ,2^63 -1]
Float(M,D) 4 单精度浮点数。天缘博客提醒这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。
Double(M,D) 8  双精度浮点。
Decimal(M,D) M+1或M+2 未打包的浮点数,用法类似于FLOAT和DOUBLE,天缘博客提醒您如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。
Date 3 以YYYY-MM-DD的格式显示,比如:2009-07-19
Date Time 8 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2009-07-19 11:22:30
TimeStamp 4 以YYYY-MM-DD的格式显示,比如:2009-07-19
Time 3 以HH:MM:SS的格式显示。比如:11:22:30
Year 1 以YYYY的格式显示。比如:2009
Char(M) M
定长字符串。
VarChar(M) M 变长字符串,要求M<=255
Binary(M) M 类似Char的二进制存储,特点是插入定长不足补0
VarBinary(M) M 类似VarChar的变长二进制存储,特点是定长不补0
Tiny Text Max:255 大小写不敏感
Text Max:64K 大小写不敏感
Medium Text Max:16M 大小写不敏感
Long Text Max:4G 大小写不敏感
TinyBlob Max:255 大小写敏感
Blob Max:64K 大小写敏感
MediumBlob Max:16M 大小写敏感
LongBlob Max:4G 大小写敏感
Enum 1或2 最大可达65535个不同的枚举值
Set 可达8 最大可达64个不同的值
Geometry    
Point    
LineString    
Polygon    
MultiPoint    
MultiLineString    
MultiPolygon    
GeometryCollection    

  在上图的最后一行的值只适用在"非严格模式",关于严格模式后面讲到。在“开发基础系列4“ 中讲到CHAR 列删除了尾部的空格。
由于char是固定长度,所以字的处理速度比varchar快,但也浪费存储空间,随着mysql 的不断升级,varchar数据类型的性能也在不断改进提高,varchar的字节是L+1字节,1字节是用来记录其长度的字节。

浮点数和定点数

  1. 浮点数一般用于表示含有小数点的数值。当一个字段被定义为浮点类型后,如果插入数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际定义的精度值
  2. 定点数是以字符串形式存放的,所以定点数可以更精确的保存数据
  3. 浮点数会产生误差,避免与浮点数的比较

数据类型的选择

  • 更小的通常更快,性能更佳原因在于占用更少的磁盘,内存,cpu缓存并且需要处理的cpu周期也更小。但是要确认你没有低估存储值的范围
  • 尽量避免NULL,相对于mysql来说,他更难优化,因为NULL的列使得索引、索引统计和值比较都更为复杂。
  • 在mysql中,整型的计算一般使用bigint(64位)来计算,即使是32位的环境也是如此。但是一些聚合函数例外,他们使用decimal和double来计算
  • decimal类型通常用来存储精确小数,因为他支持高精确计算(5.0及其以后)

三、使用建议

1、在指定数据类型的时候一般是采用从小原则,比如能用TINY INT的最好就不用INT,能用FLOAT类型的就不用DOUBLE类型,这样会对MYSQL在运行效率上提高很大,尤其是大数据量测试条件下。

2、不需要把数据表设计的太过复杂,功能模块上区分或许对于后期的维护更为方便,慎重出现大杂烩数据表

3、数据表和字段的起名字也是一门学问

4、设计数据表结构之前请先想象一下是你的房间,或许结果会更加合理、高效

5、数据库的最后设计结果一定是效率和可扩展性的折中,偏向任何一方都是欠妥的

 

  char长度可选范围在0-255之间,也就是char最大能存储255个字符,varchar的长度范围为0-65535个字节。

日期类型选择

  1. 根据实际需要选择能够满足应用的最小存储的日期类型
  2. 如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用 datetime,而不要使用 timestamp。因为 timestamp 表示的日期范围比 datetime 要短的多
  3. 如果记录的日期需要让不同时区的用户使用,那么最好使用 timestamp,因为日期类型中只有它能够和实际的时区相对应

索引的建立

  • 尽量避免在可为NULL的列上创建索引

选择数据类型的基本原则

前提:使用适合存储引擎。

选择原则:根据选定的存储引擎,确定如何选择合适的数据类型。

下面的选择方法按存储引擎分类:

  • MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
  • MEMORY存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
  • InnoDB 存储引擎和数据列:建议使用 VARCHAR类型。

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

下面说一下固定长度数据列与可变长度的数据列。

  下面重点区别下mysql与sql server中对varchar(n) n的区别

应该使用整型来存储IP地址?

通常情况下,人们使用varchar(15)来存储IPv4的地址,事实上,他们是32位的无符号整型,而不是字符串,用小数点分割成四部分只是为了让人们简单易懂,所以应该用无符号整型来存储ip地址,mysql同时也提供了INET_ATON()和INET_NTOA()函数在这两者之间转换。

char与varchar

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

CHAR(4) 存储需求 VARCHAR(4) 存储需求
'' '    ' 4个字节 '' 1个字节
'ab' 'ab  ' 4个字节 'ab ' 3个字节
'abcd' 'abcd' 4个字节 'abcd' 5个字节
'abcdefgh' 'abcd' 4个字节 'abcd' 5个字节

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值保存**,并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)
 
mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab  +          | ab+            |
+----------------+----------------+
1 row in set (0.00 sec)

  -- 在sql server中varchar(10) 10代表字节数,而不是字符数, 一个汉字二个字节所以最多放五个汉字
  INSERT INTO table_1 values('你好中国人民')

如何在数据库中存储钱?

将需要存储的货币单位更加小数的位数乘以相应的倍数即可,假设,我们要存储到万分之一分,那么我们就把所有的金额乘以一百万,将结果存储在bigint中,这样就可以避免浮点存储计算不精确和decimal精确计算代价高的问题。

text和blob

 

在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能。

①BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞",以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.

②使用合成的(synthetic)索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值,并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是,我们要注意这种技术只能用于精确匹配的查询(散列值对于类似<或>=等范围搜索操作符 是没有用处的)。我们可以使用MD5()函数生成散列值,也可以使用SHA1()或CRC32(),或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样,如果散列算法生成的字符串带有尾部空格,就不要把它们存储在CHAR或VARCHAR列中,它们会受到尾部空格去除的影响。

合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

③在不必要的时候避免检索大型的BLOB或TEXT值。例如,SELECT *查询就不是很好的想法,除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列,决定那些需要的数据行,然后从合格的数据行中检索BLOB或 TEXT值。

④把BLOB或TEXT列分离到单独的表中。在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中 的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的碎片,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

金沙棋牌app手机下载 2

数据库中DATETIME和TIMESAMP的区别?

他们都可以存储相同的数据类型:时间和日期,精确到秒。
但是区别:

  • TIMESAMP只使用DATETIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。
  • TIMESAMP允许的时间范围要小很多,所以有时候他上面的特殊能力可能为成为他的障碍。
  • TIMESAMP能表示从1970年1月1日午夜以来的秒数到2038年,而DATETIME能保存1001年到9999年,精度为秒。

浮点数与定点数

为了能够引起大家的重视,在介绍浮点数与定点数以前先让大家看一个例子:
mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

从上面的例子中我们看到c1列的值由131072.32变成了131072.31,这就是浮点数的不精确性造成的。

在mysql中float、double(或real)是浮点数,decimal(或numberic)是定点数。

浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中,大家要记住以下几点:

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

 

ref:

  --  在mysql中varchar(10) 10代表字符数,而不是字节数,一个汉字是1个字符所以最多可放10个汉字
  INSERT INTO Myisam_char VALUES('你好中国人民','你好中国人民')

在数据库中如何保存到微秒级别的时间戳?

答案是:使用bigint类型存储微秒级别的时间戳也可以切换到mariadb来代替mysql

金沙棋牌app手机下载 3

varchar和char的区别?

varchar:

varchar类型存储的是可变长的字符串,他比定长类型的更节省空间,因为他只用必要的空间,字符越短使用越少的空间,但是有一种情况例外,就是使用ROW_FORMAT=FIXED创建的话,每一行都使用的定长存储,这很浪费空间,另外varchar需要一到两个字节额外记录字符串的长度,如果列小于255个字节,那么使用一个字节,否则使用两个。假设使用latin1字符集,则一个varchar(10)的列需要11个字节的存储空间,而varchar(20000)需要20002个字节,因为需要存储长度信息。

varchar虽然节省了空间,对性能有帮助,但由于是变长的,在update时可能会让行比原来更长,那么这就有额外的工作,这个行占用的空间增长,并且在此页内没有更多的空间存储,这种情况下,不同的存储引擎的处理方式不一样,比如:myisam将拆成不同的片段存储,innodb则需要分裂页使得行可以放在列内,总之,就是加重了碎片化。

char:
char是定长的,mysql会根据定义的字符串长度来分配足够的空间,当存储char的时候,mysql会删除末尾的空格,且char值会根据采用空格进行填充

总结:
varchar适合

  • 字符串列的最大长度比平均长度大很多,列更新很少的情况,这样碎片化不是问题,就像使用utf-8这样复杂的字符集,每个字符度使用不同的字节数进行存储。
  • 注意:innodb会把过长的varchar处理成blob

char适合

  • 存储很短的字符串或者所有的值接近同一个长度,例如char比较适合存储密码md5值,因为这是一个定长的值。
  • 对于经常变更的数据,char也比varchar更好,因为定长的不容易产生碎片。
  • 对于非常短的列,char比varchar在存储空间上更有效率,因为varchar会需要额外字节来记录长度。

个人博客:yulibaozi.com

在mysql中,不同的存储引擎对char和varchar的使用原则有所不同,这里简单概括下:
  myisam  存储引擎:建议使用固定长度的数据列代替可变长度的数据列。
  memory  存储引擎:目前都使用固定长度的数据行存储,因此无论使用char或varchar列都没有关系,两者都是作为char类型处理。
  innodb    存储引擎:建议使用varchar类型,在innodb内部行存储格式没有区分固定长度和可变长度。

二.  text与blob 

  二者通常用来保存较大文本,如文章或日记, 主要差别是BloB能用来保存二进制数据如照片,text只能保存字符数据。
  这里介绍blob与text存在的一些常见问题:

  1. "空洞"性能问题

      在大量删除操作时,数据表中会留下很大的"空洞",以后填入这些空洞的记录在插入的性能上会有影响,为了提高性能,建议定期使用optimize table功能对表来进行碎片整理。下面来验证下

-- 创建表t
CREATE TABLE t(id VARCHAR(1000),context TEXT);
-- 往里面插入大量数据
INSERT INTO t VALUES(1,REPEAT('haha',100));
INSERT INTO t VALUES(2,REPEAT('haha',100));
INSERT INTO t VALUES(3,REPEAT('haha',100));
INSERT INTO t SELECT * FROM t;
-- ...
INSERT INTO t SELECT * FROM t;

--  获取表的空间大小 176.70 MB
SELECT (data_length/1024.0/1024.0) 'data_MB',
index_length  FROM information_schema.tables 
WHERE table_schema='test' AND table_name = 't';  

金沙棋牌app手机下载 4

-- 从表中删除ID为1的数据,占1/3 (共 131072 行受到影响)
DELETE FROM  t WHERE id=1;
--  再次获取表的空间大小, 是176.68 MB,并没有因为数据删除而减少

金沙棋牌app手机下载 5

-- 使用optimize table 优化
OPTIMIZE TABLE t;

金沙棋牌app手机下载 6

--  再次获取表的空间大小, 是134.65MB 发现表的数据大大缩小了,"空洞"被回收了 

金沙棋牌app手机下载 7

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:选择合适的数据类型,MySQL中数据类型介绍金沙棋

关键词: