CODEWITHSUNDEEP
mysqlcollation
Ricardo stands with Ukraine
Ricardo stands with Ukraine

Reputation: 4822

MySql remove collation spec from table and columns

In my DB some tables and columns were defined with collations explicitly:

CREATE TABLE `MyTable` (
  `MyTableId` int(11) NOT NULL AUTO_INCREMENT,
  `CommandName` varchar(255) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`MyTableId`),
  ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Now defined the default charset and collation at database level

ALTER DATABASE `MyDatabase` CHARACTER SET latin1 COLLATE latin1_general_ci;

is it possible to remove the collations (without having to recreate the tables) so it is clear that is using default setting from database. When script the table want to have this instead:

CREATE TABLE `MyTable` (
  `MyTableId` int(11) NOT NULL AUTO_INCREMENT,
  `CommandName` varchar(255) NOT NULL,
  PRIMARY KEY (`MyTableId`),
  ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=latin1;

Tried without specifying the collation value but when script the database is the same

ALTER TABLE MyTable MODIFY CommandName varchar(255)

Upvotes: 5

Views: 4069

Answers (1)

Rick James
Rick James

Reputation: 142208

The charset and collation on each column is the important stuff. The settings on the table and database are only defaults for when you add new columns or tables, repsectively.

To see the default collation for a given charset (such as latin1):

mysql> SHOW COLLATION LIKE '%latin1%';

+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+

Note that if you say just CHARACTER SET latin1, you get COLLATION latin1_swedish_ci.

So, your Question, as stated, is on how to change from latin1_general_ci to latin1_swedish_ci. If that is really what you want, then it requires rebuilding the table(s). Any CHARACTER SET or COLLATION change on a column requires a rebuilt. Changing a default does is less invasive.

It is best to explicitly specify CHARACTER SET and COLLATION for each column, thereby avoiding having to know the subtle issues of "defaults".

Bottom line (based on the Question as it stands):

ALTER TABLE MyTable
    MODIFY `CommandName` varchar(255) COLLATE latin1_general_ci NOT NULL;

Upvotes: 4

Related Questions