Are table names in MySQL case sensitive?

MysqlCase SensitiveMysql5

Mysql Problem Overview

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.

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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

To resolve the issue, set the lower_case_table_names to 1

> lower_case_table_names=1

This will make all your tables lowercase, no matter how you write them.

Solution 3 - Mysql

It depends upon lower_case_table_names system variable:

show variables where Variable_name='lower_case_table_names'

There are three possible values for this:

  • 0 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.
  • 1 - Table names are stored in lowercase on disk and name comparisons are not case sensitive.
  • 2 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive.

Documentation

Solution 4 - Mysql

Table names in MySQL are file system entries, so they are case insensitive if the underlying file system is.

Solution 5 - Mysql

  1. Locate the file at /etc/mysql/my.cnf

  2. Edit the file by adding the following lines:

     [mysqld]

 lower_case_table_names=1

  3. sudo /etc/init.d/mysql restart

  4. Run mysqladmin -u root -p variables | grep table to check that lower_case_table_names is 1 now

You might need to recreate these tables to make it work.

Categories
Recommended Mysql Solutions
Recommended Case Sensitive Solutions
Recommended Mysql5 Solutions

Previous Solution:

OpenJDK availability for Windows OS

JavaWindows

Next Solution:

How can I check if a var is a string in JavaScript?

JavascriptStringVariable Types

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionbenstpierreView Question on Stackoverflow
Solution 1 - MysqlCloudyMarbleView Answer on Stackoverflow
Solution 2 - MysqlStephenLembertView Answer on Stackoverflow
Solution 3 - MysqlRaman SahasiView Answer on Stackoverflow
Solution 4 - MysqlOswaldView Answer on Stackoverflow
Solution 5 - Mysqlsendon1982View Answer on Stackoverflow