fullpacodd

• Mysql Database Name Case Sensitive

Jul 10, 2012  Removing the 'Lower Case' in the domain is not a solution to remove the case-sensitivity of a field. This applies only to the screen fields referencing this domain. If you have an Oracle DB, you use Native SQL to perform a case insensitive search - Case insensitive search in select statement. If you change lowercasetablenames in a DB with existing tables MySQL Table doesn't exist. But it does (or it should) can happen. The comment to this answer helped me in this case: I reverted the value, restarted the database, exported the tables, set the value back to 1, restarted the database, re-imported the tables and everything worked again.

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't find tables.

I have changed my /etc/mysql/my.cnf file adding the row:

lower_case_table_names=1

But that did not change anything.

My server version is:5.1.61-0ubuntu0.11.10.1 (Ubuntu)

How can I configure MySQL to ignore case in table names?

Mysql Database Name Case Sensitive

2 Answers

Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

1 - Use mysqldump to dump each database:

Ending of fallout 4. mysqldump --databases db1 > db1.sql

mysqldump --databases db2 > db2.sql

.. Do this for each database that must be recreated.

2 - Use DROP DATABASE to drop each database.

3 - Stop the server, set lower_case_table_names in the [mysqld] section of your etcmysqlmy.cnf file, and restart the server.

4 - Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

AaronAaron

File in etc/my.cnfFind this # The MySQL server [mysqld]and set lower_case_table_names = 1

There is no need to drop db. It works so first check with this.Reason is default value of lower_case_table_names =1 for Windows.

Not the answer you're looking for? Browse other questions tagged mysqlmysql-5linuxmysql-5.1case-sensitive or ask your own question.

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?

Somnath MulukSomnath Muluk

4 Answers

by default, MySQL does not consider the case of the strings

This is not quite true. Whenever you create database in MySQL, the database/schema has a character set and a collation. Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

This has an effect on things like grouping and equality. For example,

In a case-sensitive database, we get:

While in a case-insensitive database, we get:

Note that you can also change the collation from within a query. For example, in the case-sensitive database, I can do

Matt FenwickMatt Fenwick

You always should state with your question which version of MySQL you're using, because MySQL is in steady development.

Okay, back to your question:

The string functions in MySQL are always case sensitive, so you coulduse any of the functions LOCATE, POSITION, or INSTR.

For example:

The pattern matching with regular expression (RLIKE or REGEXP) isalways case sensitive for all versions of MySQL except the newest3.23.4.

For example:

For both the normal comparison (=) and the SQL pattern matching (LIKE) the behaviour depends on the fields that are involved:

a. CHAR, VARCHAR, and all variants of TEXT fields do compare case insensitive.

b. CHAR BINARY, VARCHAR BINARY and all variants of BLOB fields do compare case sensitive.

If you compare a field from (a) with a field from (b), then the comparison will be case sensitive (case sensitivity wins). See chapter '7.2.7 String types' of the MySQL Reference Manual and look for the statements on sorting and comparisons.

Starting with V3.23.0 it's also possible to force a comparison into case sensitivity with the cast operator BINARY, independent of the types of involved fields. See chapter '7.3.7 Cast operators' of the MySQL Reference Manual.

So you also might change the type of user_name, or with V3.23.x try something like:

RolandoMySQLDBA

In my situation, I'm using Access 2010, but I had the same problem yet the solution is different: Use the StrComp() function and test its return to be zero.

StrComp( thing, 'abc', 0) = 0

Because StrComp() returns -1 if the first argument is 'smaller', 1 if it is 'larger', and 0 if it is 'equal', when StrComp()=0, you have a case-sensitive match.

See here, here or here.

Try this for insensitive search, it works fine with great performance:

Not the answer you're looking for? Browse other questions tagged mysqlselectcase-sensitive or ask your own question.