MySql remove collation spec from table and columns

Question

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)

Possible duplicate of [How to change the default collation of a database?](https://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database) — Sebastian Brosch, Nov 21 '17 at 14:38
@SebastianBrosch, no, it's not a duplicated of this post. As I say tried with ALTER but does not produce the results I want — Ricardo stands with Ukraine, Nov 21 '17 at 15:28

score 4 · Answer 1 · edited Mar 26 '19 at 17:33

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;

A sentence like: `ALTER TABLE \`MyTable\` CONVERT TO CHARACTER SET latin1;` Could you change the `CommandName` column from `latin1_general_ci` to `latin1_swedish_ci` without rebuilding?. — wchiquito, Nov 21 '17 at 21:53
Changing a column's collation, _by any means_ necessarily requires rebuilding any index containing that contains that column. I do not know if a rebuild is necessary when the column is not used in any index. — Rick James, Nov 21 '17 at 22:02
`It is best to explicitly specify CHARACTER SET and COLLATION for each column, thereby avoiding having to know the subtle issues of "defaults".` - can you give some details or links about those "subtle issues"? I'm genuinely interested. — nevvermind, Apr 24 '18 at 12:07
@nevvermind - Maybe nothing subtle... It will acquire defaults for unspecified charset/collation. But I am pretty sure the setting is then frozen for the column (in the case of adding a column). — Rick James, Apr 25 '18 at 18:13

MySql remove collation spec from table and columns

1 Answers1