Mysql 中Set字段类型实践

今天特地研究了一下Mysql的set字段，也看了enum类型字段，本Blog重点写Set字段的使用。

先创建一个表

CREATE TABLE `TestSet` (  `Id` int(4) NOT NULL AUTO_INCREMENT,  `set1` set('ABC','1111','2222','XXX') DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

测试1： 查询后SET返回的类型与长度

测试程序如下

 Connection con = DriverManager.getConnection(url, user, password);        Statement st = con.createStatement();        ResultSet rs = st.executeQuery("select * from TestSet");        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {            System.out.print(rs.getMetaData().getColumnLabel(i));            System.out.print(" ColumnName: ");            System.out.print(rs.getMetaData().getColumnName(i));            System.out.print(" ColumnDisplaySize:");            System.out.print(rs.getMetaData().getColumnDisplaySize(i));            System.out.print(" ColumnTypeName: ");            System.out.print(rs.getMetaData().getColumnTypeName(i));            System.out.print("  Precision: ");            System.out.print(rs.getMetaData().getPrecision(i));            System.out.print("  Scale: ");            System.out.print(rs.getMetaData().getScale(i));            System.out.print(" \n");        }        System.out.println("\n--------------------");        while (rs.next()) {            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {                System.out.print(rs.getString(rs.getMetaData().getColumnLabel(i)));                System.out.print(", ");            }            System.out.println("");        }

运行结果：

Id ColumnName: Id ColumnDisplaySize:4 ColumnTypeName: INT  Precision: 4  Scale: 0
set1 ColumnName: set1 ColumnDisplaySize:17 ColumnTypeName: CHAR  Precision: 17  Scale: 0

17 char 代表，14个可选值的总长3+3+4+4=14，再中3个逗号分隔符

测试2： 插入数据

INSERT INTO `testset` VALUES ('1', '2222，1111');INSERT INTO `testset` VALUES ('1', '1111,2222');

SELECT结果：

1, 1111,2222,
2, 1111,2222,

SET内的数据顺序是由列表顺序决定的

INSERT INTO `testset` VALUES ('3', 'ABCD');

出错：

[SQL] INSERT INTO `testset` VALUES ('3', 'ABCD');
[Err] 1265 - Data truncated for column 'set1' at row 1

不能插入非SET中定义好的值 

测试3： 修改表结构

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('ABCD','1111','2222','XXX') ;

[SQL] ALTER TABLE `testset`
MODIFY COLUMN `set1`  set('ABCD','1111','2222','XXX') ;
Affected rows: 2
Time: 0.059ms

修改没有使用的值是可以的

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('ABCD','11114444','2222','XXX') ;

[SQL] ALTER TABLE `testset`
MODIFY COLUMN `set1`  set('ABCD','11114444','2222','XXX') ;
[Err] 1265 - Data truncated for column 'set1' at row 1

不可以修改已经使用过的值

测试4： Set的最大值

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('ABCD','1111','2222','XXX','123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345') ;

做SELECT查询后，ResultSet的结构：

Id ColumnName: Id ColumnDisplaySize:4 ColumnTypeName: INT  Precision: 4  Scale: 0
set1 ColumnName: set1 ColumnDisplaySize:765 ColumnTypeName: CHAR  Precision: 765  Scale: 0

Set中可保存很长的字串

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('ABCD','1111','2222','XXX','ABCD') ;

[SQL] ALTER TABLE `testset`
MODIFY COLUMN `set1`  set('ABCD','1111','2222','XXX','ABCD') ;
[Err] 1291 - Column 'set1' has duplicated value 'ABCD' in SET

SET中不能使用重复值

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('V1',  'V2', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23', 'V24', 'V25', 'V26', 'V27', 'V28', 'V29', 'V30','V31', 'V32', 'V33', 'V34', 'V35', 'V36', 'V37', 'V38', 'V39', 'V40', 'V41', 'V42', 'V43', 'V44', 'V45', 'V46', 'V47', 'V48', 'V49', 'V50', 'V51', 'V52', 'V53', 'V54', 'V55', 'V56', 'V57', 'V58', 'V59', 'V60', 'V61', 'V62', 'V63', 'V64') ;

[SQL] ALTER TABLE `testset`
MODIFY COLUMN `set1`  set('V1',  'V2', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10',
'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20',
'V21', 'V22', 'V23', 'V24', 'V25', 'V26', 'V27', 'V28', 'V29', 'V30',
'V31', 'V32', 'V33', 'V34', 'V35', 'V36', 'V37', 'V38', 'V39', 'V40',
'V41', 'V42', 'V43', 'V44', 'V45', 'V46', 'V47', 'V48', 'V49', 'V50',
'V51', 'V52', 'V53', 'V54', 'V55', 'V56', 'V57', 'V58', 'V59', 'V60',
'V61', 'V62', 'V63', 'V64') ;
Affected rows: 0
Time: 0.005ms

ALTER TABLE `testset`MODIFY COLUMN `set1`  set('V1',  'V2', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20', 'V21', 'V22', 'V23', 'V24', 'V25', 'V26', 'V27', 'V28', 'V29', 'V30','V31', 'V32', 'V33', 'V34', 'V35', 'V36', 'V37', 'V38', 'V39', 'V40', 'V41', 'V42', 'V43', 'V44', 'V45', 'V46', 'V47', 'V48', 'V49', 'V50', 'V51', 'V52', 'V53', 'V54', 'V55', 'V56', 'V57', 'V58', 'V59', 'V60', 'V61', 'V62', 'V63', 'V64', 'V65') ;

[SQL] ALTER TABLE `testset`
MODIFY COLUMN `set1`  set('V1',  'V2', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', 'V10',
'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V20',
'V21', 'V22', 'V23', 'V24', 'V25', 'V26', 'V27', 'V28', 'V29', 'V30',
'V31', 'V32', 'V33', 'V34', 'V35', 'V36', 'V37', 'V38', 'V39', 'V40',
'V41', 'V42', 'V43', 'V44', 'V45', 'V46', 'V47', 'V48', 'V49', 'V50',
'V51', 'V52', 'V53', 'V54', 'V55', 'V56', 'V57', 'V58', 'V59', 'V60',
'V61', 'V62', 'V63', 'V64', 'V65') ;
[Err] 1097 - Too many strings for column set1 and SET
最多有64个选项

测试5： 查询

先输入3条记录

INSERT INTO `testset` VALUES ('1', 'V1,V2');INSERT INTO `testset` VALUES ('2', 'V10,V20');INSERT INTO `testset` VALUES ('3', 'V5,V10');

select * from TestSet Where set1 = 'V1,V2'

select * from testset where set1 > 'V1' -- 这里只做字串比较

select * from testset where set1 >= 'V100000' --这句也可执行

支持字串直接查询

select * from TestSet Where set1 Like 'V1%'

支持Like查询

select Id,set1 from TestSet Where set1 =3

select * from testset where set1 > 1

也可以用数值查询，3代表的选项的BIT掩码

UPDATE  TestSet SET SET1= 1  WHERE Id = 1

UPDATE testset SET Set1 = 7 & ~1  WHERE Id = 1;

也可以通过BIT掩码修改选项