精华内容
下载资源
问答
  • MySQL常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。...

    MySQL的常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型,有时我们需要对数据做更细致的管理,比如枚举和集合,就需要复合类型ENUM和SET了。

    ENUM 枚举类型

    ENUM适合于只能在一组固定值中选一个的场景,比如性别只能为男或者女。

    ENUM的优势在于:

    只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。

    但是它的使用有很多需要我们注意的地方,一不小心你就会得到错误的结果。

    使用ENUM枚举类型

    mysql> create table test (name varchar(40), sex enum('male', 'female') );

    mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');

    mysql> select * from test;

    +------+--------+

    | name | sex |

    +------+--------+

    | a | male |

    | b | female |

    | c | male |

    +------+--------+

    3 rows in set (0.00 sec)

    创建枚举类型时,我们使用关键字enum,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。如果插入值的大小写不匹配,会自动转换成枚举值。

    ENUM类型数据存储的实际值是索引值

    我们所有枚举值都是按照枚举值列表中的索引值进行存储的,如上面的ENUM('male', 'female')的sex字段所有值为:

    字面值

    存储值

    NULL

    NULL

    0

    ‘male’

    1

    ‘female’

    2

    因此如果有1000条记录都存储为male,我们可能认为数据库存储了4000个字符,其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。

    我们可以用两个例子测试下:

    mysql> select * from test where sex=1;

    +------+------+

    | name | sex |

    +------+------+

    | a | male |

    | c | male |

    +------+------+

    2 rows in set (0.00 sec)

    mysql> select name, sex+0 from test;

    +------+-------+

    | name | sex+0 |

    +------+-------+

    | a | 1 |

    | b | 2 |

    | c | 1 |

    +------+-------+

    3 rows in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():

    mysql> select name, avg(sex) from test;

    +------+--------------------+

    | name | avg(sex) |

    +------+--------------------+

    | a | 1.3333333333333333 |

    +------+--------------------+

    1 row in set (0.00 sec)

    读写时不要使用数字

    由于上面介绍的用索引值存储的特性,我们不要用枚举类型来存储数字格式的列,否则会引起很大的混淆,如:

    mysql> create table test2 (numbers enum('0', '1', '2'));

    Query OK, 0 rows affected (0.04 sec)

    # 此时2被当做索引值,因此是'1';'2'就是'2';'3'因为不是合法值,会用索引值尝试,因此是'2'

    mysql> insert into test2 (numbers) values (2), ('2'), ('3');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +---------+

    | numbers |

    +---------+

    | 1 |

    | 2 |

    | 2 |

    +---------+

    3 rows in set (0.00 sec)

    枚举类型的默认值

    即便一列被设定为枚举类型,但依然有额外两种值为合法值:NULL和''。

    当我们插入一个非法值时,在宽松模式下,会插入一个普通的空字符'',其值为0。而在严格模式下会报错。

    当该字段设定为允许为空时,NULL字段可以被正常插入。当不允许为空时,如果你不填值,会使用默认值:枚举值的第一个,如上面的male。

    除了设置为严格模式,否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < '' = 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:

    order by cast(col as char)

    或者

    order by concat(col)

    枚举值声明的限制

    创建数据类型时,枚举值不允许为表达式,如:

    mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1

    枚举值数量的限制

    枚举值用1-2个字节来存储,因此上限值为2^16-1=65535。

    SET集合类型

    SET和ENUM类型非常相似,它适合于只能在一组固定值中选零个或多个的场景,比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个,也可以都不喜欢。

    SET的优势和ENUM也相似,在于:

    只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。但在枚举值数量很多,而枚举值字符数少时这一可能不成立。

    使用SET枚举类型

    mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));

    Query OK, 0 rows affected (0.04 sec)

    mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | a | red |

    | b | red,green |

    | c | green,blue,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    创建时,我们使用关键字set,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。

    SET类型数据存储的实际值是索引值的和

    我们所有枚举值都是按照列表中的索引值进行存储的,不同的是通过设置二进制数为1的位置,即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为:

    枚举值

    二进制值

    十进制数字

    red

    0001

    1

    green

    0010

    2

    blue

    0100

    4

    yellow

    1000

    8

    而当有多个值时,通过所有值的求和得到存储的值。因此存储的数据量变少了,当取出的时候编码过的数字又会被转义成实际的字符串。

    我们可以用两个例子测试下:

    mysql> select name,color+0 from test2;

    +------+---------+

    | name | color+0 |

    +------+---------+

    | a | 1 |

    | b | 3 |

    | c | 14 |

    +------+---------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color=14;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    +------+-------------------+

    1 row in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():

    mysql> select avg(color) from test2;

    +------------+

    | avg(color) |

    +------------+

    | 6 |

    +------------+

    1 row in set (0.00 sec)

    插入时的顺序和次数

    当插入值时,set类型不关注你插入的顺序和一个枚举值的插入次数,它会自动去重并进行求和得到值,等到取出时,会按照声明的顺序返回:

    mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');

    Query OK, 1 row affected (0.00 sec)

    mysql> select name,color from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | d | red,green,yellow |

    +------+-------------------+

    4 rows in set (0.00 sec)

    查找集合值

    由于set类型的特殊性,因此有专用的查找函数:

    mysql> select * from test2 where find_in_set('red', color);

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    # 这一种方法当出现lightred颜色的时候就无法正确工作了

    mysql> select * from test2 where color like '%red%';

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    集合值的计算方式是位运算

    前面说是对枚举值去重并自动求和只是为了方便理解,实际上是进行位运算,得到最终的值,如0001 + 0100 = 0101。

    因此我们也可以用类似的方法来查找值:

    mysql> select name,color from test2 where color & 10;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | b | red,green |

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color & 12;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    2 rows in set (0.00 sec)

    上面的这个&符号什么含义我没查到,但我猜测这个&符号的含义就是位运算,当两个数在一个位置都为1时返回true,如果没有一个位置两者都为1则为false。

    具体的可以计算下:a,b,c,d分别为0001,0011,1110,1011,此时10为1100,12为1010,可以计算的到上面的结果。其他数字的结果也都符合,所以应该符合我的猜测。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:

    order by cast(col as char)

    或者

    order by concat(col)

    枚举值数量的限制

    枚举值用1-8个字节来存储,因此上限值为8*8=64个。

    参考资料

    作者:赵帅强

    展开全文
  • MySQL常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。...

    MySQL的常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型,有时我们需要对数据做更细致的管理,比如枚举和集合,就需要复合类型ENUM和SET了。

    ENUM 枚举类型

    ENUM适合于只能在一组固定值中选一个的场景,比如性别只能为男或者女。

    ENUM的优势在于:只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。

    但是它的使用有很多需要我们注意的地方,一不小心你就会得到错误的结果。

    使用ENUM枚举类型mysql> create table test (name varchar(40), sex enum('male', 'female') );

    mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');

    mysql> select * from test;

    +------+--------+

    | name | sex |

    +------+--------+

    | a | male |

    | b | female |

    | c | male |

    +------+--------+

    3 rows in set (0.00 sec)

    创建枚举类型时,我们使用关键字enum,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。如果插入值的大小写不匹配,会自动转换成枚举值。

    ENUM类型数据存储的实际值是索引值

    我们所有枚举值都是按照枚举值列表中的索引值进行存储的,如上面的ENUM('male', 'female')的sex字段所有值为:字面值存储值

    NULLNULL

    ''0

    'male'1

    'female'2

    因此如果有1000条记录都存储为male,我们可能认为数据库存储了4000个字符,其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。

    我们可以用两个例子测试下:mysql> select * from test where sex=1;

    +------+------+

    | name | sex |

    +------+------+

    | a | male |

    | c | male |

    +------+------+

    2 rows in set (0.00 sec)

    mysql> select name, sex+0 from test;

    +------+-------+

    | name | sex+0 |

    +------+-------+

    | a | 1 |

    | b | 2 |

    | c | 1 |

    +------+-------+

    3 rows in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():mysql> select name, avg(sex) from test;

    +------+--------------------+

    | name | avg(sex) |

    +------+--------------------+

    | a | 1.3333333333333333 |

    +------+--------------------+

    1 row in set (0.00 sec)

    读写时不要使用数字

    由于上面介绍的用索引值存储的特性,我们不要用枚举类型来存储数字格式的列,否则会引起很大的混淆,如:mysql> create table test2 (numbers enum('0', '1', '2'));

    Query OK, 0 rows affected (0.04 sec)

    # 此时2被当做索引值,因此是'1';'2'就是'2';'3'因为不是合法值,会用索引值尝试,因此是'2'

    mysql> insert into test2 (numbers) values (2), ('2'), ('3');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +---------+

    | numbers |

    +---------+

    | 1 |

    | 2 |

    | 2 |

    +---------+

    3 rows in set (0.00 sec)

    枚举类型的默认值

    即便一列被设定为枚举类型,但依然有额外两种值为合法值:NULL和''。

    当我们插入一个非法值时,在宽松模式下,会插入一个普通的空字符'',其值为0。而在严格模式下会报错。

    当该字段设定为允许为空时,NULL字段可以被正常插入。当不允许为空时,如果你不填值,会使用默认值:枚举值的第一个,如上面的male。除了设置为严格模式,否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < '' = 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:order by cast(col as char)

    或者

    order by concat(col)

    枚举值声明的限制

    创建数据类型时,枚举值不允许为表达式,如:mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1

    枚举值数量的限制

    枚举值用1-2个字节来存储,因此上限值为2^16-1=65535。

    SET集合类型

    SET和ENUM类型非常相似,它适合于只能在一组固定值中选零个或多个的场景,比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个,也可以都不喜欢。

    SET的优势和ENUM也相似,在于:只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。但在枚举值数量很多,而枚举值字符数少时这一可能不成立。

    使用SET枚举类型mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));

    Query OK, 0 rows affected (0.04 sec)

    mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | a | red |

    | b | red,green |

    | c | green,blue,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    创建时,我们使用关键字set,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。

    SET类型数据存储的实际值是索引值的和

    我们所有枚举值都是按照列表中的索引值进行存储的,不同的是通过设置二进制数为1的位置,即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为:枚举值二进制值十进制数字

    red00011

    green00102

    blue01004

    yellow10008

    而当有多个值时,通过所有值的求和得到存储的值。因此存储的数据量变少了,当取出的时候编码过的数字又会被转义成实际的字符串。

    我们可以用两个例子测试下:mysql> select name,color+0 from test2;

    +------+---------+

    | name | color+0 |

    +------+---------+

    | a | 1 |

    | b | 3 |

    | c | 14 |

    +------+---------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color=14;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    +------+-------------------+

    1 row in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():mysql> select avg(color) from test2;

    +------------+

    | avg(color) |

    +------------+

    | 6 |

    +------------+

    1 row in set (0.00 sec)

    插入时的顺序和次数

    当插入值时,set类型不关注你插入的顺序和一个枚举值的插入次数,它会自动去重并进行求和得到值,等到取出时,会按照声明的顺序返回:mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');

    Query OK, 1 row affected (0.00 sec)

    mysql> select name,color from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | d | red,green,yellow |

    +------+-------------------+

    4 rows in set (0.00 sec)

    查找集合值

    由于set类型的特殊性,因此有专用的查找函数:mysql> select * from test2 where find_in_set('red', color);

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    # 这一种方法当出现lightred颜色的时候就无法正确工作了

    mysql> select * from test2 where color like '%red%';

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    集合值的计算方式是位运算

    前面说是对枚举值去重并自动求和只是为了方便理解,实际上是进行位运算,得到最终的值,如0001 + 0100 = 0101。

    因此我们也可以用类似的方法来查找值:mysql> select name,color from test2 where color & 10;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | b | red,green |

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color & 12;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    2 rows in set (0.00 sec)

    上面的这个&符号什么含义我没查到,但我猜测这个&符号的含义就是位运算,当两个数在一个位置都为1时返回true,如果没有一个位置两者都为1则为false。

    具体的可以计算下:a,b,c,d分别为0001,0011,1110,1011,此时10为1010,12为1100,当& 10时,也就是只需要第1位或第3位存在1时(为真)就算匹配成功,从第1位到第4位(高位到低位)分别是yellow,blue,green,red,所以这个语句类似于select name,color from test2 where find_in_set('yellow', color) or find_in_set('green', color);

    可以计算的到上面的结果,其他数字的结果也都符合。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:order by cast(col as char)

    或者

    order by concat(col)

    枚举值数量的限制

    枚举值用1-8个字节来存储,因此上限值为8*8=64个。

    参考资料

    展开全文
  • Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。ENUM枚举类型ENUM适合于只能...

    Number/Date/String

    ,而String

    类型中又包含了Char/Varchar/Binary/blob/text

    等长度不同的简单数据类型,有时我们需要对数据做更细致的管理,比如枚举和集合,就需要复合类型ENUM

    和SET

    了。

    ENUM

    枚举类型

    ENUM

    适合于只能在一组固定值中选一个的场景,比如性别只能为男或者女。

    ENUM

    的优势在于:

    只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。

    但是它的使用有很多需要我们注意的地方,一不小心你就会得到错误的结果。

    使用ENUM

    枚举类型

    mysql> create table test (name varchar(40), sex enum('male', 'female') );

    mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');

    mysql> select * from test;

    +------+--------+

    | name | sex |

    +------+--------+

    | a | male |

    | b | female |

    | c | male |

    +------+--------+

    3 rows in set (0.00 sec)

    创建枚举类型时,我们使用关键字enum

    ,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。如果插入值的大小写不匹配,会自动转换成枚举值。

    ENUM

    类型数据存储的实际值是索引值

    我们所有枚举值都是按照枚举值列表中的索引值进行存储的,如上面的ENUM('male', 'female')

    的sex

    字段所有值为:

    字面值

    存储值

    NULL

    NULL

    0

    ‘male’

    1

    ‘female’

    2

    因此如果有1000

    条记录都存储为male

    ,我们可能认为数据库存储了4000

    个字符,其实只存储了1000

    个1

    字符。而在查询的时候又会将这个编码过的数字转为实际的值。

    我们可以用两个例子测试下:

    mysql> select * from test where sex=1;

    +------+------+

    | name | sex |

    +------+------+

    | a | male |

    | c | male |

    +------+------+

    2 rows in set (0.00 sec)

    mysql> select name, sex+0 from test;

    +------+-------+

    | name | sex+0 |

    +------+-------+

    | a | 1 |

    | b | 2 |

    | c | 1 |

    +------+-------+

    3 rows in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()

    和AVG()

    mysql> select name, avg(sex) from test;

    +------+--------------------+

    | name | avg(sex) |

    +------+--------------------+

    | a | 1.3333333333333333 |

    +------+--------------------+

    1 row in set (0.00 sec)

    读写时不要使用数字

    由于上面介绍的用索引值存储的特性,我们不要用枚举类型来存储数字格式的列,否则会引起很大的混淆,如:

    mysql> create table test2 (numbers enum('0', '1', '2'));

    Query OK, 0 rows affected (0.04 sec)

    # 此时2被当做索引值,因此是'1';'2'就是'2';'3'因为不是合法值,会用索引值尝试,因此是'2'

    mysql> insert into test2 (numbers) values (2), ('2'), ('3');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +---------+

    | numbers |

    +---------+

    | 1 |

    | 2 |

    | 2 |

    +---------+

    3 rows in set (0.00 sec)

    枚举类型的默认值

    即便一列被设定为枚举类型,但依然有额外两种值为合法值:NULL

    和''

    当我们插入一个非法值时,在宽松模式下,会插入一个普通的空字符''

    ,其值为0

    。而在严格模式下会报错。

    当该字段设定为允许为空时,NULL

    字段可以被正常插入。当不允许为空时,如果你不填值,会使用默认值:枚举值的第一个,如上面的male

    除了设置为严格模式,否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。

    枚举类型的排序

    常规使用order by

    进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < '' = 0 < 1 < 2

    如果希望按照文本类型进行排序,可以使用:

    order by cast(col as char)

    或者

    order by concat(col)

    枚举值声明的限制

    创建数据类型时,枚举值不允许为表达式,如:

    mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1

    枚举值数量的限制

    枚举值用1-2

    个字节来存储,因此上限值为2^16-1=65535

    SET

    集合类型

    SET

    和ENUM

    类型非常相似,它适合于只能在一组固定值中选零个或多个的场景,比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个,也可以都不喜欢。

    SET

    的优势和ENUM

    也相似,在于:

    只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。但在枚举值数量很多,而枚举值字符数少时这一可能不成立。

    使用SET

    枚举类型

    mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));

    Query OK, 0 rows affected (0.04 sec)

    mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | a | red |

    | b | red,green |

    | c | green,blue,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    创建时,我们使用关键字set

    ,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。

    SET

    类型数据存储的实际值是索引值的和

    我们所有枚举值都是按照列表中的索引值进行存储的,不同的是通过设置二进制数为1

    的位置,即2

    的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')

    的color

    字段所有值为:

    枚举值

    二进制值

    十进制数字

    red

    0001

    1

    blue

    0010

    2

    green

    0100

    4

    yellow

    1000

    8

    而当有多个值时,通过所有值的求和得到存储的值。因此存储的数据量变少了,当取出的时候编码过的数字又会被转义成实际的字符串。

    我们可以用两个例子测试下:

    mysql> select name,color+0 from test2;

    +------+---------+

    | name | color+0 |

    +------+---------+

    | a | 1 |

    | b | 3 |

    | c | 14 |

    +------+---------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color=14;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    +------+-------------------+

    1 row in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()

    和AVG()

    mysql> select avg(color) from test2;

    +------------+

    | avg(color) |

    +------------+

    | 6 |

    +------------+

    1 row in set (0.00 sec)

    插入时的顺序和次数

    当插入值时,set

    类型不关注你插入的顺序和一个枚举值的插入次数,它会自动去重并进行求和得到值,等到取出时,会按照声明的顺序返回:

    mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');

    Query OK, 1 row affected (0.00 sec)

    mysql> select name,color from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | d | red,green,yellow |

    +------+-------------------+

    4 rows in set (0.00 sec)

    查找集合值

    由于set

    类型的特殊性,因此有专用的查找函数:

    mysql> select * from test2 where find_in_set('red', color);

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    # 这一种方法当出现lightred颜色的时候就无法正确工作了

    mysql> select * from test2 where color like '%red%';

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    集合值的计算方式是位运算

    前面说是对枚举值去重并自动求和只是为了方便理解,实际上是进行位运算,得到最终的值,如0001 + 0100 = 0101

    因此我们也可以用类似的方法来查找值:

    mysql> select name,color from test2 where color & 10;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | b | red,green |

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color & 12;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    2 rows in set (0.00 sec)

    上面的这个&

    符号什么含义我没查到,但我猜测这个&

    符号的含义就是位运算,当两个数在一个位置都为1

    时返回true

    ,如果没有一个位置两者都为1

    则为false

    具体的可以计算下:a,b,c,d

    分别为0001,0011,1110,1011

    ,此时10

    为1100

    ,12

    为1010

    ,可以计算的到上面的结果。其他数字的结果也都符合,所以应该符合我的猜测。

    枚举类型的排序

    常规使用order by

    进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < 0 < 1 < 2

    如果希望按照文本类型进行排序,可以使用:

    order by cast(col as char)

    或者

    order by concat(col)

    枚举值数量的限制

    枚举值用1-8

    个字节来存储,因此上限值为8*8=64

    个。

    参考资料

    注意:本文来自SegmentFault博客。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证,请您自行验证核实并承担相关的风险与后果!

    CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场,本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题,请通过[eMail]联系我们处理,如需商业授权请联系原作者/原网站。

    展开全文
  • MySQL常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。...

    MySQL的常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同的简单数据类型,有时我们需要对数据做更细致的管理,比如枚举和集合,就需要复合类型ENUM和SET了。

    ENUM 枚举类型

    ENUM适合于只能在一组固定值中选一个的场景,比如性别只能为男或者女。

    ENUM的优势在于:只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。

    但是它的使用有很多需要我们注意的地方,一不小心你就会得到错误的结果。

    使用ENUM枚举类型mysql> create table test (name varchar(40), sex enum('male', 'female') );

    mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');

    mysql> select * from test;

    +------+--------+

    | name | sex |

    +------+--------+

    | a | male |

    | b | female |

    | c | male |

    +------+--------+

    3 rows in set (0.00 sec)

    创建枚举类型时,我们使用关键字enum,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。如果插入值的大小写不匹配,会自动转换成枚举值。

    ENUM类型数据存储的实际值是索引值

    我们所有枚举值都是按照枚举值列表中的索引值进行存储的,如上面的ENUM('male', 'female')的sex字段所有值为:字面值存储值

    NULLNULL

    ''0

    'male'1

    'female'2

    因此如果有1000条记录都存储为male,我们可能认为数据库存储了4000个字符,其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。

    我们可以用两个例子测试下:mysql> select * from test where sex=1;

    +------+------+

    | name | sex |

    +------+------+

    | a | male |

    | c | male |

    +------+------+

    2 rows in set (0.00 sec)

    mysql> select name, sex+0 from test;

    +------+-------+

    | name | sex+0 |

    +------+-------+

    | a | 1 |

    | b | 2 |

    | c | 1 |

    +------+-------+

    3 rows in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():mysql> select name, avg(sex) from test;

    +------+--------------------+

    | name | avg(sex) |

    +------+--------------------+

    | a | 1.3333333333333333 |

    +------+--------------------+

    1 row in set (0.00 sec)

    读写时不要使用数字

    由于上面介绍的用索引值存储的特性,我们不要用枚举类型来存储数字格式的列,否则会引起很大的混淆,如:mysql> create table test2 (numbers enum('0', '1', '2'));

    Query OK, 0 rows affected (0.04 sec)

    # 此时2被当做索引值,因此是'1';'2'就是'2';'3'因为不是合法值,会用索引值尝试,因此是'2'

    mysql> insert into test2 (numbers) values (2), ('2'), ('3');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +---------+

    | numbers |

    +---------+

    | 1 |

    | 2 |

    | 2 |

    +---------+

    3 rows in set (0.00 sec)

    枚举类型的默认值

    即便一列被设定为枚举类型,但依然有额外两种值为合法值:NULL和''。

    当我们插入一个非法值时,在宽松模式下,会插入一个普通的空字符'',其值为0。而在严格模式下会报错。

    当该字段设定为允许为空时,NULL字段可以被正常插入。当不允许为空时,如果你不填值,会使用默认值:枚举值的第一个,如上面的male。除了设置为严格模式,否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < '' = 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:order by cast(col as char)

    或者

    order by concat(col)

    枚举值声明的限制

    创建数据类型时,枚举值不允许为表达式,如:mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1

    枚举值数量的限制

    枚举值用1-2个字节来存储,因此上限值为2^16-1=65535。

    SET集合类型

    SET和ENUM类型非常相似,它适合于只能在一组固定值中选零个或多个的场景,比如一个人喜欢的颜色可以为红、黄、蓝等颜色中的一个或多个,也可以都不喜欢。

    SET的优势和ENUM也相似,在于:只能在固定值中选择,可以在数据库层面限制非法值。

    数据的存储用数字来存储,占用空间少。但在枚举值数量很多,而枚举值字符数少时这一可能不成立。

    使用SET枚举类型mysql> create table test2 (name varchar(40), color set('red', 'green', 'blue', 'yellow'));

    Query OK, 0 rows affected (0.04 sec)

    mysql> insert into test2(name,color) values ('a', 'red'), ('b', 'red,green'), ('c', 'green,blue,yellow');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select * from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | a | red |

    | b | red,green |

    | c | green,blue,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    创建时,我们使用关键字set,同时跟着一组可枚举值列表,这些可枚举值必须使用字符串的格式,否则会报错。

    SET类型数据存储的实际值是索引值的和

    我们所有枚举值都是按照列表中的索引值进行存储的,不同的是通过设置二进制数为1的位置,即2的幂次方。如上面的SET('red', 'blue', 'green', 'yellow')的color字段所有值为:枚举值二进制值十进制数字

    red00011

    green00102

    blue01004

    yellow10008

    而当有多个值时,通过所有值的求和得到存储的值。因此存储的数据量变少了,当取出的时候编码过的数字又会被转义成实际的字符串。

    我们可以用两个例子测试下:mysql> select name,color+0 from test2;

    +------+---------+

    | name | color+0 |

    +------+---------+

    | a | 1 |

    | b | 3 |

    | c | 14 |

    +------+---------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color=14;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    +------+-------------------+

    1 row in set (0.00 sec)

    这种存储和查询的方式会导致一些处理数字的函数,也会使用存储的值来进行计算,如SUM()和AVG():mysql> select avg(color) from test2;

    +------------+

    | avg(color) |

    +------------+

    | 6 |

    +------------+

    1 row in set (0.00 sec)

    插入时的顺序和次数

    当插入值时,set类型不关注你插入的顺序和一个枚举值的插入次数,它会自动去重并进行求和得到值,等到取出时,会按照声明的顺序返回:mysql> insert into test2(name,color) values ('d', 'yellow,green,red,yellow');

    Query OK, 1 row affected (0.00 sec)

    mysql> select name,color from test2;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | d | red,green,yellow |

    +------+-------------------+

    4 rows in set (0.00 sec)

    查找集合值

    由于set类型的特殊性,因此有专用的查找函数:mysql> select * from test2 where find_in_set('red', color);

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    # 这一种方法当出现lightred颜色的时候就无法正确工作了

    mysql> select * from test2 where color like '%red%';

    +------+------------------+

    | name | color |

    +------+------------------+

    | a | red |

    | b | red,green |

    | d | red,green,yellow |

    +------+------------------+

    3 rows in set (0.00 sec)

    集合值的计算方式是位运算

    前面说是对枚举值去重并自动求和只是为了方便理解,实际上是进行位运算,得到最终的值,如0001 + 0100 = 0101。

    因此我们也可以用类似的方法来查找值:mysql> select name,color from test2 where color & 10;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | b | red,green |

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    3 rows in set (0.00 sec)

    mysql> select name,color from test2 where color & 12;

    +------+-------------------+

    | name | color |

    +------+-------------------+

    | c | green,blue,yellow |

    | d | red,green,yellow |

    +------+-------------------+

    2 rows in set (0.00 sec)

    上面的这个&符号什么含义我没查到,但我猜测这个&符号的含义就是位运算,当两个数在一个位置都为1时返回true,如果没有一个位置两者都为1则为false。

    具体的可以计算下:a,b,c,d分别为0001,0011,1110,1011,此时10为1010,12为1100,当& 10时,也就是只需要第1位或第3位存在1时(为真)就算匹配成功,从第1位到第4位(高位到低位)分别是yellow,blue,green,red,所以这个语句类似于select name,color from test2 where find_in_set('yellow', color) or find_in_set('green', color);

    可以计算的到上面的结果,其他数字的结果也都符合。

    枚举类型的排序

    常规使用order by进行排序时,会按照字母的文本顺序。但枚举类型由于存储为索引值,因此会按照索引值进行排序:NULL < 0 < 1 < 2。

    如果希望按照文本类型进行排序,可以使用:order by cast(col as char)

    或者

    order by concat(col)

    枚举值数量的限制

    枚举值用1-8个字节来存储,因此上限值为8*8=64个。

    参考资料

    展开全文
  • Char:当我们需要涉及一些长度固定的数据列时可以使用char数据类型,比如手机号使用char(11),身份证号使用char(18),用户性别使用char(3)。 varchar:以定义一个可变长度的字符串,理论情况下可存储最多255个字节...
  • SQL Server数据类型

    2021-04-09 22:07:28
    4.固定长度非unicode字符:char,最多可以存8000个字节,使用场景用来做公司名的数据类型 5.可变长度非unicode字符:varchar这是常用的一种数据库类型,它可以根据实际存储的字符去改变存储空间,一个字符占一个字节...
  • charvarchar:汉字占两个字节,英文、数字或字符占一个 比如: 性别:男 女 可以定义为:char(2)或者是varchar(2) 因为性别是中文,中文占两个字节 ...固定长度与非固定长度: 身份证号:18位...
  • 金蝶常用数据类型

    2018-07-09 08:45:54
    1、 char(n) 定长字符类型(可以用于身份证类,或者性别;季节等)2、varchar(n) 长度不超过N个字符字符类型;如果n 换成MAX 即 varchar(max)最多能存储8k字节,一般用于后期开发使用3、int 数字整数类型 像...
  • Oracle有哪些数据类型

    2020-09-26 10:18:12
    固定长度的字符串 长度可以是1-2000个字节 例子: 性别 身份证号码 手机号 varchar2(M) 可变长度的字符串 长度可以是1-4000个字节 数值类型 number(p,s) p表示精度,s表示小数位数 可以存整数、也可以存浮点数...
  • 【C/C++基础】11_用户自定义数据类型

    千次阅读 2018-04-09 21:29:56
    1. 结构体类型1.1 结构体类型定义一般形式 在实际问题中,一...因为数组中各元素的类型和长度都必须一致,以便于编译系统处理。为了解决这个问题,C语言中给出了另一种构造数据类型——“结构(structure)”或...
  • 当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char类型。当一个字段数据长度不确定,例如:简介、姓名等都是采用varchar类型。 BLOB:二进制大对象(存储图片,视频等流媒体信息) ...
  • 数据类型 长度 键 描述 n_id 整型 主键 用户编号 n_uname varchar 10 用户名 n_密码 varchar 16 用户密码 n_phone varchar 11 用户电话 性爱 varchar 2个 用户性别 n_dept varchar 10 用户所属部门,机构 ...
  • 因为数组中各元素的类型和长度都必须一致,以便于编译系统处理。为了解决这个问题,C语言中给出了另一种构造数据类型——“结构”。 它相当于其它高级语言中记录。 “结构”是一种构造类型,它是由
  • 结构类型定义和结构变量说明 在... 因为数组中各元素的类型和长度都必须一致,以便于编译系统处理。为了解决这个问题,C语言中给出了另一种构造数据类型——“结构”。 它相当于其它高级语言中记录。 “结构”是
  • 元数据、数据元、资源目录 ...姓名、性别、学号都是数据元,但是这些数据元有自己的元数据,即描述数据,分别是长度类型、值域等。 对于学生信息表而言,姓名、性别、学号是描述学生信息的数据
  • }MySQL字段数据类型:charvarchar如何选择?当数据长度确定情况下,是定长,例如:性别、生日等采用char。当数据长度不确定情况,不定长,例如:简介、姓名等采用varchar。表名格式:t_或者 tbl...
  • 数据表基础

    2019-03-01 11:19:45
    基本数据类型 学习char:固定长度的非Unicode字符数据;varchar:可变长度的非Unicode字符数据;nchar:固定长度的Unicode数据;nvarchar:可变长度的Unicode字符数据 charvarchar:汉字占两个字节,英文、数字或...
  • 数据挖掘之数据准备

    2017-09-06 19:26:20
    两种常见的数据类型:数值型分类型 数值型值包括实型变量整数变量,比如年龄、速度或长度,数值型特征有两个重要的属性:其值有顺序关系距离关系 分类型变量只有两个值:相等或不等,再建立一种等同关系,...
  • Pandas 分类数据

    2020-04-04 13:34:16
    例如:性别,国家代码等特征总是重复。这些是分类数据例子。 分类变量只能采用有限数量,而且通常是固定数量。除了固定长度,分类数据可能有顺序,但不能执行数字操作。 分类是Pandas数据类型。 分类...
  • Pandas分类数据

    2018-09-13 16:10:00
    例如:性别,国家代码等特征总是重复。这些是分类数据例子。 分类变量只能采用有限数量,而且通常是固定数量。除了固定长度,分类数据可能有顺序,但不能执行数字操作。 分类是Pandas数据类型。 分类...
  • MySQL常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。...
  • MySQL常用数据类型包括:Number/Date/String,而String类型中又包含了Char/Varchar/Binary/blob/text等长度不同简单数据类型,有时我们需要对数据做更细致管理,比如枚举集合,就需要复合类型ENUMSET了。...
  • C#数据结构

    2013-12-10 11:49:54
    数据类型和数据结构的概念可知,二者的关系非常密切。数据类型可以看 作是简单的数据结构。数据的取值范围可以看作是数据元素的有限集合,而对数 据进行操作的集合可以看作是数据元素之间关系的集合。 数据结构...
  • 例如:性别,国家代码等特征总是重复。这些是分类数据例子。分类变量只能采用有限数量,而且通常是固定数量。除了固定长度,分类数据可能有顺序,但不能执行数字操作。 分类是Pandas数据类型。1、分类变量...
  • 什么是元数据

    2020-05-07 18:58:46
    如果把数据比作人话,元数据就像数据的“户口本”,表明数据的存储位置(家庭住址)、字段长度(年龄)、字段类型性别)、数据之间关系(亲属关系)等,让数据更容易理解、查找、管理使用。 元数据包括什么? ...
  • 数据表基本操作注意点:1、数据表中已经有数据时,轻易修改数据类型,有可能因为不同的数据类型的数据在机器中存储的方式及长度并不相同,修改数据类型可能会影响到数据表中已有的数据类型。2. 数据表名不区分大小写...
  • charvarchar区别

    2019-08-11 14:27:33
    因此char类型一般用于长度确定的数据列(比如:手机号,性别,身份证号等)。 varchar:是一个可变长度的字符串,取值范围是0~65535之间(根据数据库的编码来决定),在定义字符串列时,如果使用varch...
  • 结构化的数据:即有固定格式有限长度的数据。例如填的表格就是结构化的数据,国籍:中华人民共和国,民族:汉,性别:男,这都叫结构化数据。对于ICT领域来说,就是以固定的格式存储到数据库里的数据(Oracle/...

空空如也

空空如也

1 2 3 4 5 ... 9
收藏数 174
精华内容 69
关键字:

性别的数据类型和长度