SEARCHING DATE AND TEXT FIELDS
3.3 Case Sensitivity
Any text search in MySQL is based on the character set and collation that is being used. You can review which collations are being used in the server by issuing the following SQL statement:
mysql> SHOW VARIABLES LIKE 'coll%';
+---+---+ | Variable_name | Value | +---+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +---+---+ 3 rows in set (#.## sec)
Types of searches
Searches can be made in a case sensitive, case insensitive or binary fashion. The corresponding collations will end in _cs, _ci and _bin. The following SQL statement demonstrates how a case insensitivity collation works:
mysql> SELECT Code, Name FROM Country WHERE Code = 'Fin'; +---+---+
| Code | Name | +---+---+ | FIN | Finland | +---+---+ 1 row in set (#.## sec)
To alter the collation setting for the current connection, the following SQL can be used:
mysql> SET COLLATION_CONNECTION = latin1_general_cs;
With the current connections collation set to case-sensitive, the following SQL should produce an empty set:
mysql> SELECT Code, Name FROM Country WHERE Code = 'Fin'; +---+---+
| Code | Name | +---+---+ | FIN | Finland | +---+---+ 1 row in set (#.## sec)
The fact that the SQL statement actually worked and did not produce an empty set could be an issue if it was not for the fact that each column's defined collation can override the collation of the connection (or table, database, server, etc.).
59 60 3-4 ______________________________________________________________________________________________ ______________________________________________________________________________________________ ______________________________________________________________________________________________
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
In this case, the Country.Code column has a collation that is different from the current connections collation:
mysql> SHOW FULL COLUMNS FROM Country WHERE Field='Code'\G
*************************** 1. row *************************** Field: Code Type: char(3) Collation: latin1_swedish_ci Null: NO Key: PRI Default: Extra: Privileges: select,insert,update Comment:
1 row in set (#.## sec) CHARSET defaults
When a DEFAULT CHARSET clause is part of the table creation syntax and there is no defined collation, a default collation will be implied based on the defined character set. The default collation for the latin1 character set (and all character sets) can be seen by issuing the following SQL statement:
mysql> SHOW COLLATION;
+---+---+---+---+---+---+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+---+---+---+---+---+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | ... ... ...
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | ... ... ...
+---+---+---+---+---+---+ 126 rows in set (#.## sec)
As seen in this display, the default collation connected to the latin1 character set is latin1_swedish_ci. Thus, the collation is case insensitive for the table no matter what the connection collation may be.
61
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
Alter a column's collation
The collation for a column can be specifically set in the table definition by identifying the collation to be used either in the table creation or afterwards by altering the table:
mysql> ALTER TABLE Country MODIFY Code CHAR(3) COLLATE latin1_general_cs;
This SQL has overwritten the default collation and defined a case sensitive collation to the Country table. Issuing the same SQL presented earlier will now produce the intended result of an empty set:
mysql> SELECT Code, Name FROM Country WHERE Code = 'Fin'; Empty set (#.## sec)
To actually return a result, the SQL must be specifically set to include case sensitivity when searching against the table. The following SQL takes into account the case sensitive collation that the Country table is using:
mysql> SELECT Code, Name FROM Country WHERE Code = 'FIN'; +---+---+
| Code | Name | +---+---+ | FIN | Finland | +---+---+ 1 row in set (#.## sec)
62
3-6 ______________________________________________________________________________________________ ______________________________________________________________________________________________ ______________________________________________________________________________________________
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
3.3.1 Binary searches
The difference between binary comparisons versus collation based (text searches) is that there is no need to consider sort order or multiple characters evaluating to the same sort-value. Thus binary comparisons will produce better performance; however, all the specific features associated with language and character sets will not be available.
Binary searches are accomplished by forcing one side of the comparison operation to become binary thus forcing the whole comparison into a binary context. To demonstrate how this would work, the following SQL returns the Country table back to a case insensitive collation:
mysql> ALTER TABLE Country MODIFY Code CHAR(3) COLLATE latin1_general_ci;
With the Country table being case insensitive again, the following SQL will produce the desired output without taking into account that the actual search does not have the same case as the stored data:
mysql> SELECT Code, Name FROM Country WHERE Code = 'Fin'; +---+---+
| Code | Name | +---+---+ | FIN | Finland | +---+---+ 1 row in set (#.## sec)
By adding the BINARY clause to either side of the comparison operation, the MySQL server is forced to make the SELECT statement a binary comparison operation:
mysql> SELECT Code, Name FROM Country WHERE Code = BINARY 'Fin'; Empty set (#.## sec)
mysql> SELECT Code, Name FROM Country WHERE BINARY Code = 'Fin'; Empty set (#.## sec)
63
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
Possible poor performance
There is a possible down side to using the BINARY clause with an SQL statement that could utilize an index that is associated with the content of the comparison operation. The following SQL describes how the SQL server would execute the binary comparison operation previously shown:
mysql> EXPLAIN SELECT Code, Name FROM Country WHERE BINARY Code = 'Fin'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 239
Extra: Using where 1 row in set (#.## sec)
Even though the Code column in the Country table is the primary key, the binary comparison operation will perform a full table scan because the index was not created for binary comparisons. This can be corrected by utilizing a binary collation.
64
3-8 ______________________________________________________________________________________________ ______________________________________________________________________________________________ ______________________________________________________________________________________________