Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I specify collation when creating a table from select in mysql?

When creating a table with dynamically created and defined columns from a select result, how can I specify collation as you do when creating a table with column definitions?

i.e.

CREATE TABLE IF NOT EXISTS my_table (
  SELECT * FROM (
    SELECT ....
  ) )

The above creates the table with the dynamic columns just fine but it is not using the collation I want. It is using utf8_general_ci and I want it to be utf8_unicode_ci.

This below doesn't work

CREATE TABLE IF NOT EXISTS my_table (
  SELECT * FROM (
    SELECT ....
  ) ) DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

it gives error message indicating the commands are not valid at this position.

I realize the above suggestion is not valid, so what method for achieving this is? I've tried setting the database collation thinking this was setting a default for the database for all tables created but it doesn't appear to behave like that - or maybe I am not actually setting the default for the database when I ALTER the database collation. Is there a way to set a default for tables so they the first code block above creates the table using the desired collation?

The SELECT... results in the above code blocks refers to a variety of conditional/logical value setting using case/when statements, string parsing, and function results - it is not just selecting columns one-to-one from another table that has defined columns so it is not as simple as defining the other table's columns.

I have already tried setting the database collation ahead of time using ALTER DATABASE my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci but this doesn't change the results. The tables are still created (and the columns in the tables) using utf8_general_ci when creating a table from a select (dynamically defined columns from select results)

like image 402
Streamline Avatar asked Sep 13 '17 01:09

Streamline


People also ask

How do you set collation when creating a table?

You can set the collation by adding the T-SQL COLLATE clause to the CREATE TABLE and ALTER TABLE statements. When you use those statements, you define the column and its properties, including any collation settings.

How do you specify collation in a query?

You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.

Can you set a collation for a column?

Database collation To apply new collation to existing tables of a database, the user can set new collation on TEXT columns of tables.


2 Answers

CREATE TABLE my_table (
    ... none, some, or all columns ...
) ... none, some, or all specifications ...  -- Here
    SELECT ...;

If that fails, then

CREATE TABLE my_table ( all columns ) all specs;
INSERT INTO my_table
    SELECT ...;

Or...

CREATE ... SELECT ...;
ALTER TABLE my_table ...;  -- any fixes needed

Yet another approach is to make sure that the DATABASE has the DEFAULT CHARACTER SET and/or DEFAULT COLLATION desired before the CREATE TABLE ....

Whatever you do, follow it with

SHOW CREATE TABLE my_table;

to verify that each column has desired CHARACTER SET and COLLATION. If missing from the column, then look at the table's DEFAULTs.

Example

CREATE TABLE `us` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `country` char(2) CHARACTER SET ascii NOT NULL COMMENT 'ISO 3166 Country Code',
  `ascii_city` varchar(100) CHARACTER SET ascii NOT NULL COMMENT 'Name of city or town in ASCII encoding',
  `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `state` char(2) CHARACTER SET ascii NOT NULL COMMENT 'For US, ISO-3166-2 code for the state/province name.',
  `population` int(10) unsigned NOT NULL DEFAULT '0',
  `lat` float NOT NULL,
  `lng` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `state` (`state`,`city`),
  KEY `state_2` (`state`,`population`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Notice the explicit CHARACTER SET and COLLATE on some columns, thereby overriding the DEFAULT CHARSET of the table.

When there is no collation, CHARSET utf8 defaults to COLLATE utf8_general_ci.

Example 2

In this, I will create a new table us2 from that table, but change the collation of city:

CREATE TABLE us2 (
  `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
)
    SELECT * from us;

Everything about us2 will be the same as for us except for the collation of that one column.

like image 160
Rick James Avatar answered Sep 28 '22 06:09

Rick James


The following did work for me on MariaDB version 10:

CREATE TABLE my_table ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SELECT *
FROM other_table
;
like image 35
giordano Avatar answered Sep 28 '22 07:09

giordano