title: MySQL 5.6 create view with unicode character set
body: <p>MySQL 5.6. I can't get a string constant within a view to populate correctly against a database with default UCS2 character set. Works fine on 5.7.</p>
  
  <p>I've created a minimally reproducible example, below.</p>
  
  <pre><code>DROP SCHEMA IF EXISTS test3;
  CREATE SCHEMA test3 CHARACTER SET ucs2;
  CONNECT test3;
  
  CREATE TABLE testtable (
  testname VARCHAR(15)
  );
  
  INSERT INTO testTable( testname ) VALUES ('foo');
  INSERT INTO testTable( testname ) VALUES ('bar');
  
  CREATE OR REPLACE VIEW testview AS
  SELECT * FROM testtable
  WHERE testname = 'foo';
  
  SELECT * FROM testview;
  </code></pre>
  
  <p>^^^ This select statement returns no results.<p></p>
  
  <pre>
  MySQL [test3]> show create view testview \G
  *************************** 1. row ***************************
                  View: testview
           Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
  SQL SECURITY DEFINER VIEW `testview` AS select `testtable`.`testname` AS 
  `testname` from `testtable` where (`testtable`.`testname` = '\0\0\0f\0\0\0o\0\0\0o')
  character_set_client: utf8
  collation_connection: utf8_general_ci
  </pre>
  
  <p>What is that, utf32??</p>
  
  <p>The following <em>does</em> work, but I don't want to write the collation directly into the statement, as this needs to be portable code and the syntax looks non-standard:</p>
  
  <pre><code>CREATE OR REPLACE VIEW testview AS
  SELECT * FROM testtable
  WHERE testname = 'foo' COLLATE utf8_general_ci;
  </code></pre>
  
  <p>I have tried setting the client, connection, and server character sets to ucs2 and utf16 but this changed nothing. Likewise with the collations to *_general_ci.
  <p><p>
  Any ideas?
  <p>
  Edit:
  <p></p>
  
  <pre>MySQL [test3]> show variables like "char%";
  +--------------------------+------------------------------------------------------------+
  | Variable_name            | Value                                                      |
  +--------------------------+------------------------------------------------------------+
  | character_set_client     | utf8                                                       |
  | character_set_connection | utf8                                                       |
  | character_set_database   | ucs2                                                       |
  | character_set_filesystem | binary                                                     |
  | character_set_results    | utf8                                                       |
  | character_set_server     | latin1                                                     |
  | character_set_system     | utf8                                                       |
  | character_sets_dir       | C:\Program Files\MySQL\mysql-5.6.36-winx64\share\charsets\ |
  +--------------------------+------------------------------------------------------------+
  </pre>