Asked  7 Months ago    Answers:  5   Viewed   71 times

Are table names in MySQL case sensitive?

On my Windows development machine the code I have is able to query my tables which appear to be all lowercase. When I deploy to the test server in our datacenter the table names appear to start with an uppercase letter.

The servers we use are all on Ubuntu.

 Answers

76

In general:

Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names.

One can configure how tables names are stored on the disk using the system variable lower_case_table_names (in the my.cnf configuration file under [mysqld]).

Read the section: 10.2.2 Identifier Case Sensitivity for more information.

Tuesday, June 1, 2021
 
BetaRide
answered 7 Months ago
77

Rules for naming objects, including tables in MySql:

http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So this would be invalid:

 SELECT * FROM 12345;

But the following would be valid:

 SELECT * FROM `12345`;

Or if running in ANSI mode the following would work:

SET @@session.sql_mode=ANSI_QUOTES;
SELECT * FROM "12345";
Friday, June 4, 2021
 
Whakkee
answered 7 Months ago
72

(updating @ÁlvaroG.Vicario answer and comments, and complementing this answer... This is a Wiki, please edit to enhance)

Example: for CSS3 (and HTML5) there are new explicit rules, as "font-face property must be case-insensitive".[2]


Context

W3C interoperating standards, mainly XML, HTML, CSV and CSS.

CSS general rules

CSS2 (a W3C standard of 2008) fixed basic conventions about "Characters and case", and CSS3 (a W3C standard for 2015) added something more.

  1. By default "all CSS syntax is case-insensitive (...)" [1]

  2. There are exceptions, "(...) except for parts that are not under the control of CSS"[1]

    2.1. element names are case-sensitive in HTML5 (?) and XML, but case-insensitive in HTML4.

    2.2. identifiers (including element names, classes, and IDs in selectors) are case-sensitive. HTML attributes id and class, of font names, and of URIs lies outside the scope of the CSS specification.

  3. ....

  4. The

Case matrix

Exceptions and specific (explicited in a reference) rules. "YES" indicate that value is case-sensitive.

Property values:

 CSS property      | Case-sens. | Reference and notes
 ------------------|------------|--------------------
 %colorVals        | NO         | [3]
 font-family       | NO         | [2]
 %url              | YES        | ...
 content           | YES        | ...
 ----------------------------------------------------
 %colorVals = color, background, etc.
 %url = background-image, etc. that use `url()`, see [7] and notes. 

Selector values:

 CSS selector      | Case-sens. | Reference and notes
 ------------------|------------|--------------------
 id                | YES        |...
 element           | YES/NO     | ... YES for XML...
 class name        | YES        | [5]
 (`~ i` operator)  | NO         | [6]
 ----------------------------------------------------
 YES/NO = depends on the document language (see ref. and notes).

REFERENCES:

[1] W3C/CSS2/syndata, sec. 4.1.3 Characters and case.

[2] W3C/CSS3-fonts, sec. 5.1 Case sensitivity of font family names

[3] W3C/CSS3-color, sec. 4.1. Basic color keywords

[4] W3C/CSS3-values, sec. 3.1. Pre-defined Keywords

[5] W3C/Selectors, sec. 3. Case sensitivity

[6] W3C/Selectors4, sec. 6.3. Case-sensitivity

[7] RFC 3986 and URL syntax illustration at Wikipedia.


Quotations and notes

  • Typical URLs starts with domain, that is case insensitive, but after it (path, query or fragment syntatical components), is case sensitive. See [7].

  • "User agents must match these names case insensitively". [2]

Thursday, July 22, 2021
 
pocketfullofcheese
answered 5 Months ago
33

you should use case sensitive collation, for example

SELECT *
FROM `facilitydb_login`
WHERE facilitydb_login.`password` collate latin1_general_cs = 'PASS'

CS in the name means that it's Case Sensitive

Saturday, November 27, 2021
 
Smitha
answered 1 Week ago
71

They should be exactly the same. Probably you might want to read the corresponding section from the MySQL manual (which is only about syntax, not about performance, however).

Monday, November 29, 2021
 
adelbertc
answered 1 Week ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share