MySQL数据类型

发表于

数据类型

数据类型分类

数值类型

类型 字节 最小值 最大值
(带符号/无符号) (带符号/无符号)
TINYINT 1 -128（-2^7） 127（2^7-1）
0 255（2^8-1）
SMALLINT 2 -32768（-2^15） 32767（2^15-1）
0 65535（2^16-1）
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

  • 在MySQL中，整形是可以指定是有符号和无符号，默认是有符号的。

  • 通过UNSINGNED来表明字段是无符号的

    1
    mysql> create table tt2(num tinyint unsigned);
    • 注意：尽量不要使用unsingned，对于int来兴可能放不下的数据，unsingned int同样可能存放不下，还不如在设计时将int提升为bigint类型，但是对于优化器而言，unsingned确实有一定的用处，比如当你要查询的数据为负数时，unsingned会直接返回，并不会进行查询。

bit类型

  • 语法：

    1
    bit[(M)]：位字段类型。M表示每个值的位数，范围从1到64.如果M被忽略，默认为1

    范例：

    1
    mysql> create table tt2 (id int,a bit(64));

  • bit字段在显示时，是按照ASCII码对应的值显示。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> insert into tt2 values(65,65);
    Query OK, 1 row affected (0.11 sec)
    mysql> select * from tt2;
    +------+----------+
    | id   | a        |
    +------+----------+
    |   65 |        A |
    +------+----------+

  • 如果我们只需要在表中存放0或1，这时就可以定义bit(1)。这样就可以节省空间。

    1
    mysql> create table tt3 (a bit(1));

  • 使用十进制形式查看表中数据

    • 语法：
    1
    select 字段名称+0 from 表名称：
    1
    2
    3
    4
    5
    6
    mysql> select a+0 from tt2;
    +------+
    | a+0  |
    +------+
    |   65 |
    +------+

  • 使用其它方式产看表中数据

    • 二进制查看表中数据
    1
    select bin(字段名称) from 表名称;
    • 八进制查看表中数据
    1
    select oct(字段名称) from 表名称;
    • 十六进制查看表中数据
    1
    select hex(字段名称) from 表名称;

    注意：使用以上函数查看表中数据存储的数据类型时，是不能食用引索查询的

小数类型

float：

  • 语法：

    1
    float[(m,d)][unsigned]：m定点数指定显示长度，d指定小数位数，占用空间4个字节

    • float(4,2)表示的范围是：-99.99~99.99，MySQL在保存值会进行四舍五入

      1
      mysql> create table tt4 (id int,a float(4,2) unsigned);

关于符号问题：

  • 当将数据定义为float(4,2) unsingned时，由于是无符号，范围是0~99.99

  • 当向表中插入一个负数时，比如-0.1，就会直接保存（超出范围），而不是进行四舍五入

    • 但是当我们要强制将数插入到表中时，可以使用如下语法：
    1
    insert ignore into 表名称 values(id:100,data:-0.1);

    范例：

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> insert ignore into tt4 values(100,-0.1);
    Query OK, 1 row affected, 1 warning (0.14 sec)
    mysql> select * from tt4;
    +------+------+
    | id   | a    |
    +------+------+
    |  100 | 0.00 |
    +------+------+
decimal：

  • 语法：

    1
    decimal(m,d) [unsigned]：定点数m指定长度，d指定小数位数

decimal与float的区别：

  • 精度不一样：float表示的进度大约是小数点后7位，高于7位就有可能造成进度的丢失（关于精度丢失，由于二进制小数点一直不能乘到1，小数点后面总会有数字），decimal表示的精度更加准确，M最大为65，D最大为30，它是将数值以字符串的形式存储的。
    • 建议：如果希望小数的精度高，推荐使用decimal

字符串类型

char：

  • 语法：

    1
    char(L)：固定长度字符串，L是可以存储的长度，单位为字符，最大长度可以为255
    • 注意：char最多规定255个字符长度
varchar：

  • 语法：

    1
    varchar(L)：可变长度字符串，L表示字符长度，最大长度65535个字节

    关于varchar(len)，len到底为多大，该len值和标的编码密切相关：

    • varchar长度可以指定为0~65535之间的值，但是其中有1~2个字节用于记录数据大小

    • 当使用表的编码是utf8时，varchar(n)的参数n最大值为65535/2=21844（因为utf8中，每个每个字符占用3个字节），如果编码为gbk，varchar(n)的参数n最大值为65535/2=32766（在gbk编码中，一个字节占用2个字节）。

      1
      mysql> create table tt6(name varchar(21844)) charset=utf8;
char和varchar比较
实际存储 char(4) varchar(4) char占用字节 varchar占用字节
abcd abcd abcd 4*3=12 4*3+1=13
A A A 4*3=12 1*3+1=4
Abcde × × 数据超出长度 数据超出长度

如何选择定长或变长字符串？

  • 如果数据长度都一样，就使用定长（char），比如：身份证，手机号，md5

    • md5是一种加密方法，加密后的数据是不能逆行使用的，也就是说要查看用户密码信息，需要通过数据查询匹配来实现

      1
      2
      3
      4
      5
      6
      7
      如果数据库表User中有一列为passwd,存放的是md5加密的数据，如何更新新的数据。
      update user set passwd=md5("123321") where uName="lihua";
      插入新的数据：
      insert into user(uName,passwd) values("xiaoqiang",md5("123321")) ;
      这样存放在数据中的密码信息就是保密存放的，但是通过md5加密后的数据是不能逆向使用的，也就是说如果查看用户的密码信息，则需要通过数据查询匹配来实现。
      比如需要进行用户身份认证，则需要执行下面查询语句：
      select * from user where uName="lihua" and passwd=md5("123321");

  • 如果数据有变化，就用变长(varchar)，比如：名字，地址，但是你保证最长的数据能够存进去

  • 定长的磁盘空间比较浪费，但是效率高

  • 变长的磁盘空间比较节省，但是效率低

注意：temp会根据varchar最大长度去创建临时表