I've defined a table like
CREATE TABLE `mytable` (
`identifier` varchar(45) NOT NULL,
`f1` char(1) NOT NULL,
KEY `identifier` (`identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
and then added primary key and index as
ALTER TABLE `mytable` ADD PRIMARY KEY ( `identifier` )
ALTER TABLE `mytable` ADD INDEX ( `identifier` )
In the identifier field my table is populated with values like (about 800,000 records)
USER01-TESTXXY-CAD-10172
USER01-TESTXXY-CAD-1020
USER01-TESTXXY-CAD-10245
USER02-TEST-003-SUBA
USER02-TEST-002-SUBB
I've discovered that queries where the identifier ends with a number aren't matched:
SELECT *
FROM identifier
WHERE identifier = 'USER01-TESTXXY-CAD-10245';
but queries matching an identifier which ends with letters are matched successfully
SELECT *
FROM identifier
WHERE identifier = 'USER02-TEST-003-SUBA';
My queries are exact, I don't need to compare with LIKE bec开发者_JAVA技巧ause my users provide me exact strings. Besides varchar(45) is more than enough space for my identifiers. What I did wrong? What could be the reason or solution?
I think you have made a typo in both the queries. Your table name should be mytable instead of identifier.
SELECT *
FROM mytable
WHERE identifier = 'USER01-TESTXXY-CAD-10245';
Is it a typo ? Is there another table in your database that is called as identifier?
精彩评论