title: How do I specify collation when creating a table from select in mysql?
body: <p>When creating a table with <strong>dynamically created and defined columns</strong> from a select result, how can I specify collation as you do when creating a table with column definitions?</p>
  
  <p>i.e.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS my_table (
    SELECT * FROM (
      SELECT ....
    ) )
  </code></pre>
  
  <p>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.</p>
  
  <p>This below doesn't work</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS my_table (
    SELECT * FROM (
      SELECT ....
    ) ) DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;
  </code></pre>
  
  <p>it gives error message indicating the commands are not valid at this position.</p>
  
  <p>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 <code>ALTER</code> 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?</p>
  
  <p>The <code>SELECT...</code> results in the above code blocks refers to a variety of conditional/logical value setting using <code>case/when</code> 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.</p>
  
  <p>I have already tried setting the database collation ahead of time using <code>ALTER DATABASE my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci</code> but this doesn't change the results. The tables are still created (and the columns in the tables) using <code>utf8_general_ci</code> when creating a table from a select (dynamically defined columns from select results)</p>