4

I am running mysql 5.6.

Some of the columns in a schema that I inherited from previous developers have an explicitly specified collate clause.

All explicitly specified collate clauses are the same as the database's default collate.

Is there any way to remove the explicit column collate clauses?

There should be no functional collating differences versus my current collate, but I want the following:

  1. to get column definitions sans collate clauses when I request a create table statement from mysql (I want to be able to compare table creation scripts from a code repository with create table statements obtained from different instances of the schema on different mysql servers; the explicit column collate clauses are only in some instances, but not others, which would require me to use a more complex diff than a plain text diff)

  2. to have the collate of these columns automatically change to whatever is the new default database collate if I ever change it

1) is much more important than 2), however, since I will probably never change the collate again.

Thanks.

XDR
  • 3,457
  • 3
  • 23
  • 42

1 Answers1

0

Instead of using SHOW CREATE TABLE, fetch the equivalent data from information_schema tables TABLES and COLUMNS.

Meanwhile, do you have an example of the COLLATION clause being present in some cases, but not in other cases?

Rick James
  • 106,233
  • 9
  • 103
  • 171
  • I no longer work for the company with these tables, so I don’t have examples. We had schema creation scripts, so the point was to compare the current VCS versions with whatever the running database had. Selecting info from the `information_schema` tables would have required us to reconstruct `create table` statements ourselves from the data, which would take extra effort, and possible be error-prone. Plus, that only deals with issue 1, not issue 2. – XDR May 23 '18 at 18:57