1.表结构
CREATE TABLE `demo` (
`FId` int(11) NOT NULL AUTO_INCREMENT,
`FFieldA` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`FFieldB` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`FByte` bit(1) DEFAULT NULL,
PRIMARY KEY (`FId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
表内容:
2.没有添加FByte字段索引时查询SQL
语句1:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo where FByte=ASCII(b'1');
语句2:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo where FByte=b'1';
语句3:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo where FByte=1;
语句4: select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo where FByte='1';
查询结果:都可以查询到数据
添加索引后
\
使用force index强制使用索引
语句1:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo FORCE INDEX(idx) where FByte=ASCII(b'1');
语句2:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo FORCE INDEX(idx) where FByte=b'1';
语句3:select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo FORCE INDEX(idx) where FByte=1;
语句4: select *,BIN(1),BIN(b'1'),BIN(FByte),ASCII(1),ASCII(b'1'),ASCII(BIN(1)),ASCII(BIN(b'1')),BIT_LENGTH(FByte),BIT_LENGTH(1)
FROM demo FORCE INDEX(idx) where FByte='1';
查询结果只有语句四有问题。(初步判断原因是:BIT字段添加索引后,不能默认的把字符'1'转换成数值1导致查询结果有误)。可以使用CAST('1' AS SIGNED)来进行数值转换。但是不建议,
尽量是传值和字段定义一样的类型,避免这类转换问题
和