Menu
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY .
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
MySQL queries are not case-sensitive by default. Following is a simple query that is looking for 'value'. However it will return 'VALUE', 'value', 'VaLuE', etc…
SELECT * FROM `table` WHERE `column` = 'value'
The good news is that if you need to make a case-sensitive query, it is very easy to do using the BINARY operator, which forces a byte by byte comparison:
SELECT * FROM `table` WHERE BINARY `column` = 'value'
To improve James' excellent answer:
It's better to put BINARY in front of the constant instead:
SELECT * FROM `table` WHERE `column` = BINARY 'value'
Putting BINARY in front of column will prevent the use of any index on that column.
Whilst the listed answer is correct, may I suggest that if your column is to hold case sensitive strings you read the documentation and alter your table definition accordingly.
In my case this amounted to defining my column as:
`tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
This is in my opinion preferential to adjusting your queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With