CODEWITHSUNDEEP
mysql
Cerin
Cerin

Reputation: 64739

How to Configure MySQL to be Case Sensitive

What are the commands to enable and disable case sensitivity by default for an entire database on a MySQL server? I'm aware of the COLLATE statement, but it seems it's necessary to place this in every SQL statement that's run. Is there an option to set this globally?

Upvotes: 18

Views: 48249

Answers (3)

Juan Jimenez
Juan Jimenez

Reputation: 501

The most common way to create a MySQL database with case-sensitive collation is:

CREATE DATABASE your_db_name CHARACTER SET utf8 COLLATE utf8_bin;

Upvotes: 1

NullReference
NullReference

Reputation: 2958

I've come here looking to modify the collation only for a specific column for it to be case-sensitive and not for the entire table or the database. Hope it helps someone looking just for this.

This query could be tried:

ALTER TABLE table_name MODIFY column_name column_datatype COLLATE utf8_bin;

Upvotes: 7

John Parker
John Parker

Reputation: 54445

You can set collation at both the database creation and table creation level as a part of the CREATE TABLE statement.

To set the collation for the entire database, you can use:

CREATE DATABASE test_database CHARACTER SET utf8 COLLATE utf8_general_cs;

You can also change the collation on an existing database via ALTER DATABASE. (For more information see the MySQL Database Character Set and Collation manual entry.)

If however, only a single table needs to be treated as case sensitive, you could simply use:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
  test_id bigint unsigned NOT NULL auto_increment,
  ...

  PRIMARY KEY test_id (test_id),
  ...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_cs;

(Case insensitive being "utf8_general_ci".)

Finally, the main MySQL Character Set Support manual section is probably worth a quick peruse. (It lists the character sets and collations supported by MySQL, tells you how to set the character set/collation at the server level, etc.)

Upvotes: 31

Related Questions