How to change MySQL table names in Linux server to be case insensitive?

by

MySQL’s case sensitivity is by default handled by the file system, which is why you found this difference:

9.2.2. Identifier Case Sensitivity

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 (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.8.4, “MySQL Extensions to Standard SQL”.

Fortunately, the next sentence could help you:

The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

The lower_case_table_names blurb:

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.2, “Identifier Case Sensitivity”.

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

So it appears you should set lower_case_table_names to 1 in the MySQL config file.

More Related Contents:

  1. error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’ — Missing /var/run/mysqld/mysqld.sock
  2. Can’t connect to MySQL server error 111 [closed]
  3. How do I find the MySQL my.cnf location
  4. How can I search (case-insensitive) in a column using LIKE wildcard?
  5. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  6. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  7. MySQL – ERROR 1045 – Access denied
  8. Bash script to insert values in MySQL
  9. Commit data in a mysql container
  10. libmysqlclient15-dev on macs?
  11. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  12. How to change the mysql root password
  13. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  14. Linux shell script for database backup
  15. Laravel Eloquent Ignore Casing
  16. How can I output MySQL query results in CSV format?
  17. Change limit for “Mysql Row size too large”
  18. Converting a date in MySQL from string field
  19. Ordering by specific field value first
  20. CHECK constraint in MySQL is not working
  21. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  22. Error Code 1292 – Truncated incorrect DOUBLE value – Mysql
  23. Loading utf-8 encoded text into MySQL table
  24. storing money amounts in mysql
  25. MySQL CREATE FUNCTION Syntax
  26. Difference between two table structure
  27. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  28. How to store decimal in MySQL?
  29. When to use a left outer join?
  30. MySQL cumulative sum grouped by date

Leave a Comment