PostgreSQL和MySQL like区别
前言：今天在PostgreSQL中使用like，字段类型是int，执行语句报错，
1.表结构：都是用sysbench工具产生的
postgres=# \d sbtest1;
Table "public.sbtest1"
Column | Type | Modifiers
--------+----------------+------------------------------------------------------
id | integer | not null default nextval('sbtest1_id_seq'::regclass)
k | integer | not null default 0
c | character(120) | not null default ''::bpchar
pad | character(60) | not null default ''::bpchar
Indexes:
"sbtest1_pkey" PRIMARY KEY, btree (id)
"k_1" btree (k)
MySQL
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)
2.在PostgreSQL里面执行语句
postgres=# select k from sbtest1 where k like '3%' limit 10;
ERROR: operator does not exist: integer ~~ unknown
LINE 1: select k from sbtest1 where k like '3%' limit 10;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
那么我们转换一下类型试一下，果然是可以了，那么意味着有索引页无法使用了
postgres=# select k from sbtest1 where k::text like '3%' limit 10;
k
-------
39162
39075
37436
36750
34065
37606
38584
34175
35533
38342
(10 rows)
果断全表扫描
postgres=# explain select k from sbtest1 where k::text like '3%' limit 10;
QUERY PLAN
-------------------------------------------------------------------
Limit (cost=0.00..63.64 rows=10 width=4)
-> Seq Scan on sbtest1 (cost=0.00..9577.31 rows=1505 width=4)
Filter: ((k)::text ~~ '3%'::text)
(3 rows)
3.那么来看看MySQL是怎么样子滴，
不会报错，可以执行，是不是MySQL自动转换类型了，来个执行计划就懂了
(twDB)root@localhost [sysbench]> select k from sbtest1 where k like '3%' limit 10;
+------+
| k |
+------+
| 319 |
| 3002 |
| 3073 |
| 3130 |
| 3173 |
| 3181 |
| 3211 |
| 3274 |
| 3279 |
| 3385 |
+------+
10 rows in set (0.00 sec)
哈哈，果然使用索引
(twDB)root@localhost [sysbench]> explain select k from sbtest1 where k like '3%' limit 10;
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | sbtest1 | index | k_1 | k_1 | 4 | NULL | 236197 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.03 sec)
(twDB)root@localhost [sysbench]>
结论
在PostgreSQL中，千万别对int类型进行like会无法使用索引的
