ผมขอเถียงตรงนี้ ถ้า collation เป็น xxx_ci คือ case insensitive ครับ
ถ้าเป็น xxx_bin จึงจะใช้เป็น case sensitive ได้ครับ
ตัวอย่าง Code (SQL)
CREATE TABLE IF NOT EXISTS `case_sensitive` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'To make condition on this field it must be case sensitive only.',
`lastname` varchar(100) DEFAULT NULL COMMENT 'This is case insensitive',
`varchar_longer255` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=3 ;
--
-- Dumping data for table `case_sensitive`
--
INSERT INTO `case_sensitive` (`id`, `name`, `lastname`, `varchar_longer255`) VALUES
(1, 'James', 'Watt', 'VWknAchnsjgnGjAwHsJht52ctRxPOnAWMvBKOHxk3H0taLUf3ZQ9OQGAKw07SQL2vo73cFjRIJ3CjjpA9Sg7dphGIWGhLYnEDbhOta0AdQXYoKqTjcx0T1b6CBLsUoXcVfssqQgiBlEsxPOprMwsFAZ2RcGrwa6PMIjvpHJDdICU8Slv79ASsOHktFAw24JrpNo6rTPyKtkL1vUbdk4Innn1rGKCUkMAS7Ntw9zDb9rDfwTEzc4kyYX5i4RwfXuxAdEafqR8i0tn8J9gGqvW4BB03HIYo3FJxTLst3vP9sUe'),
(2, 'Watson', 'Browney', 'ふゕぺおはどぱをくへゟへあぎしぱがゆぃゆぐぐぅごつろみへた゚くをゖせぃぬぴ゚ゎのしぽゖもでみひやまゖろめぶみゕぶじく゜ゐゆとびぺゝけゕずっめひわのあっぎっゖぷょだりけゅゕぱんぎめっばつぬほけのゖぷせおゃだかむざかをぢふめけゔぐはぺこぼがぶゅぞごぢなゟも゛えどすやゑだゟこれにのゟぐよぴゞせわねょをぇうげかごむひやはざををけあぬぉぅあかぅぽてゕねとじくほぢぇおこまじおどにろぶふれっかてゟあゃゟぎぜあほひろざ゜ぺぜしほぅぜぁらぎむゎるぞをなのぁぷっずぬゔすぶ゜ぴ゛よをおぞへしゑねへおぞぶぎきひんろ゛ゞ゚゛きでめどゅゐひゆぢきよよはゎいてゐゖっぎぞぶせほたばびがへざけしゝの゜ぬりふぼゞゑよいでて゜ぽわ');
Code (SQL)
SELECT *
FROM `case_sensitive`
WHERE `lastname` LIKE '%wat%'
LIMIT 0 , 30
Load balance : Server 05
