How to ALTER a Table to make it Case-Sensitive in MySQL?

more images

ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table.

Some operations may result in warnings if attempted on a table for which the storage engine does not support the operation. These warnings can be displayed with SHOW WARNINGS. Take a look at some of the user cases.

Eg.

ALTER TABLE user_table MODIFY user_column BLOB;
ALTER TABLE user_table MODIFY user_column VARCHAR(50) BINARY;

ALTER TABLE `User` CHANGE `password` `password` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

Now, the actual query form is as follows,

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] …

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,…)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,…) [index_type]
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,…) [index_type]
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,…) [index_type]
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
(index_col_name,…) [index_type]
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,…)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] …
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

table_options:
table_option [[,] table_option] … (see CREATE TABLE options)