infobright 字符串操作like 和 = 的区别

来源：互联网发布：网络口碑营销公司编辑：程序博客网时间：2024/04/29 01:37

之前遇到一个有趣的问题。

infobright 中有个日志表 spd_push_log_t

CREATE TABLE `spd_push_log_t` (
`record_time` datetime DEFAULT NULL COMMENT '记录时间',
`create_time` datetime DEFAULT NULL COMMENT '记录时间',
`messageid` varchar(64) DEFAULT NULL COMMENT '消息id',
`cell` varchar(64) DEFAULT NULL COMMENT '手机号，发送or接收？',
`flag` varchar(32) DEFAULT NULL,
`ip` varchar(32) DEFAULT NULL COMMENT 'OS地址',
`isoffline` varchar(32) DEFAULT NULL,
`taskid` varchar(32) DEFAULT NULL COMMENT '任务id',
`appid` varchar(256) DEFAULT NULL COMMENT '应用id'
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8 COMMENT='spd_push_log记录' ;
load data infile '/opt/sdk_data/spdlog.txt' into table spd_push_log_t fields terminated by '|';

其中有个字段 taskid

mysql> select * from spd_push_log_t where taskid = 'GT_MdSQYSGeth5eB4vqBQbSL4';
Empty set (0.08 sec)

mysql> select * from spd_push_log_t where taskid like 'GT_MdSQYSGeth5eB4vqBQbSL4';
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| record_time         | create_time         | messageid                            | cell          | flag | ip                 | isoffline | taskid                    | appid                  |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 3a4ac9cb-f133-4506-bc66-6c09ae28f55b | 1725824790779 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 7a45537f-94b5-47bf-86f9-8f87f2481966 | 1725824826743 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 324a5ce0-2ba6-4fe0-951e-31309b836d03 | 1725824811914 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | f70658c7-d840-4dab-bb22-d440577f43d0 | 1725826450524 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 316df2d6-0980-479f-ad14-4025865f6099 | 1725824757820 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 245655b3-26f0-4365-8e2a-045e51f9055a | 1725826450924 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 0374bc56-2ad3-4e02-b5fb-d487cef7e7b9 | 1725824844512 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 358b7ba7-af32-4a31-a4af-9eb45a48a3c7 | 1725827655933 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
8 rows in set (2.42 sec)

用= 和like 查询出来的结果是不同的。

in的时候也有奇怪的事情出现

mysql> select * from spd_push_log_t where taskid in( 'GT_MdSQYSGeth5eB4vqBQbSL4' );
Empty set (0.09 sec)

mysql> select * from spd_push_log_t where taskid in( 'GT_MdSQYSGeth5eB4vqBQbSL4','');
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| record_time         | create_time         | messageid                            | cell          | flag | ip                 | isoffline | taskid                    | appid                  |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 3a4ac9cb-f133-4506-bc66-6c09ae28f55b | 1725824790779 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 7a45537f-94b5-47bf-86f9-8f87f2481966 | 1725824826743 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 324a5ce0-2ba6-4fe0-951e-31309b836d03 | 1725824811914 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | f70658c7-d840-4dab-bb22-d440577f43d0 | 1725826450524 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 316df2d6-0980-479f-ad14-4025865f6099 | 1725824757820 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 245655b3-26f0-4365-8e2a-045e51f9055a | 1725826450924 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 0374bc56-2ad3-4e02-b5fb-d487cef7e7b9 | 1725824844512 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 358b7ba7-af32-4a31-a4af-9eb45a48a3c7 | 1725827655933 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
8 rows in set (2.56 sec)

in的括号中一项的时候没有记录但是有2项的时候又能查询出记录

而其他的字段又不会出现这种情况。

mysql> select * from spd_push_log_t where concat(taskid,'') = 'GT_MdSQYSGeth5eB4vqBQbSL4';
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| record_time         | create_time         | messageid                            | cell          | flag | ip                 | isoffline | taskid                    | appid                  |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 3a4ac9cb-f133-4506-bc66-6c09ae28f55b | 1725824790779 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 7a45537f-94b5-47bf-86f9-8f87f2481966 | 1725824826743 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 324a5ce0-2ba6-4fe0-951e-31309b836d03 | 1725824811914 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | f70658c7-d840-4dab-bb22-d440577f43d0 | 1725826450524 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 316df2d6-0980-479f-ad14-4025865f6099 | 1725824757820 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 245655b3-26f0-4365-8e2a-045e51f9055a | 1725826450924 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 0374bc56-2ad3-4e02-b5fb-d487cef7e7b9 | 1725824844512 | 1    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
| 2013-03-05 10:07:03 | 2013-03-05 10:07:03 | 358b7ba7-af32-4a31-a4af-9eb45a48a3c7 | 1725827655933 | 0    | 172.16.14.193:8091 | 0         | GT_MdSQYSGeth5eB4vqBQbSL4 | mYDBLd8ghT9h3ZlkjtQFE9 |
+---------------------+---------------------+--------------------------------------+---------------+------+--------------------+-----------+---------------------------+------------------------+
8 rows in set (3.37 sec)

上面的办法可以临时解决问题但是性能会有一定的影响