MYSQL版本?如果5以上,直接利用系统表 SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname AND COLUMN_NAME=colname贴建表及插入记录的SQl
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中是否有相关值存在。 不区分大小比较字段值 如果查询出结果,则认为已有同名存在。
用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>
假如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)
SELECT * FROM `smw_ids` WHERE LOWER(CONVERT(sMW_TITLE USING utf8))='test_Tom';
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';
默认校对集的不分,如果你有担心,则可以加上 upper(CONVERT(title USING utf8))=upper('test_tom')
多谢各位了。我都测了一遍,都行。但必须得将二进制转成字符串。理论上讲,字符串比较是耗时的。这可能是也是为什么人家用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效率要好一些。可能和字节长短有关吧。
SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname AND COLUMN_NAME=colname贴建表及插入记录的SQl
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中是否有相关值存在。 不区分大小比较字段值
如果查询出结果,则认为已有同名存在。
+-------+---------------+------+-----+---------+-------
| 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>
比较相关记录,直接相等不行,因为那个是二进制的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)
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';
多谢了,这个真行. 再问一下mysql默认就不区分大小写?这个受什么影响?否则哪天配置一变,我这个比较就不行了。
默认校对集的不分,如果你有担心,则可以加上 upper(CONVERT(title USING utf8))=upper('test_tom')
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效率要好一些。可能和字节长短有关吧。