mysql字段比较，查看是否存在？

[fxs_2008]

需求是给一个title查询数据库是否存在该tilte.

问题在于，该字段用varbinary类型存储。

查询时，要求比较不区分大小写。比如：存的是Test_tom, 我用Test_Tom 或 test_tom均认为存在。

求各位给个方案。

[WWWWA]

MYSQL版本？如果5以上，直接利用系统表
SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname AND COLUMN_NAME=colname

贴建表及插入记录的SQl

[ACMAIN_CHM]

你是想判断表结构中是否有这个字段？ 还是想判断表中这个字段中是否有这个记录？

问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）

[fxs_2008]

DROP TABLE IF EXISTS `smw_ids`;
CREATE TABLE `smw_ids` (
`smw_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`smw_namespace` int(11) NOT NULL,
`smw_title` varbinary(255) NOT NULL,
`smw_iw` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`smw_sortkey` varbinary(255) NOT NULL,
PRIMARY KEY (`smw_id`),
KEY `smw_title` (`smw_title`,`smw_namespace`,`smw_iw`),
KEY `smw_sortkey` (`smw_sortkey`)
) ENGINE=InnoDB AUTO_INCREMENT=379767 DEFAULT CHARSET=latin1;

相查看smw_title中是否有相关值存在。 不区分大小比较字段值
如果查询出结果，则认为已有同名存在。

[ACMAIN_CHM]

用CONVERT转一下就行了。

mysql> desc fxs_2008;
+-------+---------------+------+-----+---------+-------
| Field | Type          | Null | Key | Default | Extra
+-------+---------------+------+-----+---------+-------
| id    | int(11)       | YES  |     | NULL    |
| title | varbinary(30) | YES  |     | NULL    |
+-------+---------------+------+-----+---------+-------
2 rows in set (0.01 sec)

mysql> select * from fxs_2008;
+------+-----------+
| id   | title     |
+------+-----------+
|    1 | aaaaaaaaa |
|    2 | Test_tom  |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select *
    -> from fxs_2008
    -> where CONVERT(title USING utf8)='Test_Tom';
+------+----------+
| id   | title    |
+------+----------+
|    2 | Test_tom |
+------+----------+
1 row in set (0.00 sec)

mysql> select *
    -> from fxs_2008
    -> where CONVERT(title USING utf8)='test_tom';
+------+----------+
| id   | title    |
+------+----------+
|    2 | Test_tom |
+------+----------+
1 row in set (0.00 sec)

mysql>

[fxs_2008]

假如smw_title‘Test_tom';
比较相关记录，直接相等不行，因为那个是二进制的

SELECT * FROM `smw_ids` where smw_title='Test_Tom' ;
这个也不行
SELECT * FROM `smw_ids` where STRCMP(smw_title, 'Test_Tom') =0 ;

[小小小小周]

mysql> select * from smw_ids;
+--------+---------------+-----------+--------+-------------+
| smw_id | smw_namespace | smw_title | smw_iw | smw_sortkey |
+--------+---------------+-----------+--------+-------------+
|      1 |             1 | TT_tt_A   | a      | ASas        |
+--------+---------------+-----------+--------+-------------+
1 row in set (0.00 sec)

mysql> desc smw_ids smw_title;
+-----------+----------------+------+-----+---------+-------+
| Field     | Type           | Null | Key | Default | Extra |
+-----------+----------------+------+-----+---------+-------+
| smw_title | varbinary(255) | NO   | MUL | NULL    |       |
+-----------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from smw_ids where cast(smw_title as char)='tT_tt_a'
    -> ;
+--------+---------------+-----------+--------+-------------+
| smw_id | smw_namespace | smw_title | smw_iw | smw_sortkey |
+--------+---------------+-----------+--------+-------------+
|      1 |             1 | TT_tt_A   | a      | ASas        |
+--------+---------------+-----------+--------+-------------+
1 row in set (0.00 sec)

mysql> select * from smw_ids where cast(smw_title as char)='tt_TT_a';
+--------+---------------+-----------+--------+-------------+
| smw_id | smw_namespace | smw_title | smw_iw | smw_sortkey |
+--------+---------------+-----------+--------+-------------+
|      1 |             1 | TT_tt_A   | a      | ASas        |
+--------+---------------+-----------+--------+-------------+
1 row in set (0.00 sec)

[WWWWA]

SELECT * FROM `smw_ids` WHERE LOWER(CONVERT(sMW_TITLE USING utf8))='test_Tom';

[WWWWA]

or
SELECT * FROM `smw_ids` WHERE LOWER(CAST(sMW_TITLE AS CHAR))=lower('Test_Tom');
or
SELECT * FROM `smw_ids` WHERE (CAST(sMW_TITLE AS CHAR))='Test_Tom';

[fxs_2008]

[Quote=引用 4 楼 acmain_chm 的回复:]

用CONVERT转一下就行了。

SQL code
mysql> desc fxs_2008;
+-------+---------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------+---------------+------+-----……
[/Quote]

多谢了，这个真行.

再问一下mysql默认就不区分大小写？这个受什么影响？否则哪天配置一变，我这个比较就不行了。

[ACMAIN_CHM]

[Quote]再问一下mysql默认就不区分大小写？这个受什么影响？否则哪天配置一变，我这个比较就不行了。[/Quote]默认校对集的不分，如果你有担心，则可以加上 upper(CONVERT(title USING utf8))=upper('test_tom')

[fxs_2008]

多谢各位了。

我都测了一遍，都行。但必须得将二进制转成字符串。理论上讲，字符串比较是耗时的。这可能是也是为什么人家用varbinary的原理所在吧。只不过我的这个需求较特殊。
35万记录测试：



[SQL] SELECT * FROM `smw_ids` WHERE LOWER(CAST(smw_title AS CHAR))=lower('Test_Tom');
影响的数据栏: 0
时间: 0.483ms
[SQL] SELECT * FROM `smw_ids` where LOWER(CONVERT( smw_title USING utf8)) = LOWER('Test_Tom');
影响的数据栏: 0
时间: 0.576ms

[SQL] SELECT * FROM `smw_ids` where CONVERT( smw_title USING utf8) = 'Test_Tom';
影响的数据栏: 0
时间: 0.405ms

[SQL] SELECT * FROM `smw_ids` WHERE CAST(smw_title AS CHAR)='Test_Tom';
影响的数据栏: 0
时间: 0.358ms

用cast 转成char效率要好一些。可能和字节长短有关吧。