title: Which MySQL variable sets the default collate of local variable?
body: <p>Say I have this stored procedure:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE x(
  )
  LANGUAGE SQL
  DETERMINISTIC
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
  COMMENT ''
  BEGIN
  
  DECLARE test VARCHAR(50) CHARACTER SET 'utf8mb4';
  
  -- ...
  
  END
  </code></pre>
  
  <p>What is the mysql server variable that define the local variable <code>test</code>'s collation?</p>
  
  <p>It currently is (thanks, mysql 8.0...) <code>utf8mb4_0900_ai_ci</code> so I get yield a "nice" <code>Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='</code> in a later on code.</p>
  
  <p>Setting</p>
  
  <pre><code>DECLARE test VARCHAR(50) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
  </code></pre>
  
  <p>Removes the <code>Illegal collation</code> error, so I'm sure it's this variable that has the bad 0900 collation.</p>
  
  <p>But, no, I'm not gonna rewrite all my local variables in all my procedures to set this explicit collation. So how do I set this globally for the server? or at least, how to I set this for the connection?</p>
  
  <p>So far, I have the default mysql 8 configuration besides the following:</p>
  
  <pre><code>[mysqld]
  skip-log-bin
  innodb_buffer_pool_size = 4G
  character_set_server = utf8mb4
  collation_server = utf8mb4_general_ci
  
  # character_set_database  = utf8mb4
  # collation_database = utf8mb4_general_ci
  # character_set_client = utf8mb4
  # character_set_connection = utf8mb4
  # collation_connection = utf8mb4_general_ci
  # character_set_results = utf8mb4
  </code></pre>
  
  <p>and this statement run at client connection:</p>
  
  <pre><code>SET CHARACTER SET utf8mb4, NAMES utf8mb4, collation_connection = 'utf8mb4_general_ci', lc_messages = 'en_US', time_zone = '+00:00'
  </code></pre>
  
  <p>Commented out variables are those throwing a <code>unknown variable '...=...'</code> when trying to start mysql server, despite these variables being <a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_collation_connection" rel="nofollow noreferrer">listed in mysql 8 documentation</a> tho...</p>