- 2.1. The Server SQL Mode
- 2.2. MySQL Identifier Syntax and Naming Rules
- 2.3. Case Sensitivity in SQL Statements
- 2.4. Character Set Support
- 2.5. Selecting, Creating, Dropping, and Altering Databases
- 2.6. Creating, Dropping, Indexing, and Altering Tables
- 2.7. Obtaining Database Metadata
- 2.8. Performing Multiple-Table Retrievals with Joins
- 2.9. Performing Multiple-Table Retrievals with Subqueries
- 2.10. Performing Multiple-Table Retrievals with UNION
- 2.11. Multiple-Table Deletes and Updates
- 2.12. Performing Transactions
- 2.13. Foreign Keys and Referential Integrity
- 2.14. Using FULLTEXT Searches
This chapter is from the book
This chapter is from the book
This chapter is from the book
2.3. Case Sensitivity in SQL Statements
Case sensitivity rules in SQL statements vary for different statement elements, and also depend on what you are referring to and the operating system of the machine on which the server is running.
SQL keywords and function names. Keywords and function names are not case sensitive. They can be given in any lettercase. The following statements are equivalent:
SELECT NOW(); select now(); sElEcT nOw();
Database, table, and view names. MySQL represents databases and tables using directories and files in the underlying filesystem on the server host. As a result, the default case sensitivity of database and table names depends on how the operating system on that host treats filenames. Windows filenames are not case sensitive, so a MySQL server running on Windows does not treat database and table names as case sensitive. Servers running on Unix usually treat database and table names as case sensitive because Unix filenames are case sensitive. An exception is that names in Mac OS X Extended filesystems can be case insensitive.
MySQL represents each view using a file, so the preceding remarks about tables also apply to views.
Stored program names. Stored function and procedure names and event names are not case sensitive. Trigger names are case sensitive, which differs from standard SQL.
Column and index names. Column and index names are not case sensitive in MySQL. The following statements are equivalent:
SELECT name FROM student; SELECT NAME FROM student; SELECT nAmE FROM student;
Alias names. By default, table aliases are case sensitive. You can specify an alias in any lettercase (upper, lower, or mixed), but if you use it multiple times in a statement, you must use the same lettercase each time. If the lower_case_table_names system variable is nonzero, table aliases are not case sensitive.
String values. Case sensitivity of a string value depends on whether it is a binary or nonbinary string, and, for a nonbinary string, on the collation of its character set. This is true for literal strings and the contents of string columns. For further information, see Section 3.1.2, “String Values.”
You should consider lettercase issues when you create databases and tables on a machine with case sensitive filenames if you might someday move them to a machine where filenames are not case sensitive. Suppose that you create two tables named abc and ABC on a Unix server where those names are considered distinct. You would have problems moving the tables to a Windows machine: abc and ABC are not distinguishable because names are not case sensitive. You would also have trouble replicating the tables from a Unix master server to a Windows slave server.
To avoid having case sensitivity become an issue, pick a given lettercase and always create databases and tables using names in that lettercase. Then case of names won’t be a problem if you move a database to a different server. I recommend lowercase, particularly if you are using InnoDB tables, because InnoDB stores database and table names internally in lowercase.
To force creation of databases and tables with lowercase names even if not specified that way in CREATE statements, configure the server by setting the lower_case_table_names system variable. For more information, see Section 11.2.6, “Operating System Constraints on Database Object Names.”
Regardless of whether a database or table name is case sensitive on your system, you must refer to it using the same lettercase throughout a given query. That is not true for SQL keywords, function names, or column and index names, all of which may be referred to in varying lettercase style throughout a query.