Setting collation_connection using libmysqlclient C API
<p>I am using libmysqlclient to write a C program to inserts data (surprisingly) into a database. Character set of the <strong>table</strong> that code inserts into and it's collation are:</p>

<pre><code>CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
</code></pre>

<p>In my C program I used <code>mysql_set_character_set()</code> in order to change the character set; the problem is that this function only changes character set and not collation. If I query character set and collation using <code>mysql_get_character_set_info</code> the result is:</p>

<pre><code>char-set=utf8mb4, collation=utf8mb4_general_ci
</code></pre>

<p>According to <a href="http://dev.mysql.com/doc/refman/5.6/en/charset-connection.html" rel="nofollow">MySQL documentation</a>: </p>

<blockquote>
  <p>SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client.</p>
</blockquote>

<p>So I decided to change connection collation using <code>mysql_options</code> and <code>MYSQL_INIT_COMMAND</code>:</p>

<pre><code>SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
</code></pre>

<p>The resulting function call looks like this (I called this before <code>mysql_real_connect()</code>):</p>

<pre><code>if(mysql_options(conn, MYSQL_INIT_COMMAND, 
        "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';")) {
    // log the errors and do proper actions
}
</code></pre>

<p>But it seems that this commands are not able to change character set and collation! Because now if I query character set using <code>mysql_get_character_set_info()</code> the result is:</p>

<pre><code>char-set=latin1, collation=latin1_swedish_ci
</code></pre>

<p>The interesting thing here is that at the MySQL side if I enable logs and execute the program with <code>MYSQL_INIT_COMMAND</code> commands execute! But <code>mysql_get_character_set_info()</code> does not show this! Again according to libmysql's documentation:</p>

<blockquote>
  <p>int mysql_set_character_set(MYSQL *mysql, const char *csname)</p>
  
  <p>This function is used to set the default character set for the current
  connection. The string csname specifies a valid character set name.
  The connection collation becomes the default collation of the
  character set. This function works like the SET NAMES statement, but
  also sets the value of mysql->charset, and thus affects the character
  set used by mysql_real_escape_string()</p>
</blockquote>

<p>I want to change <strong>character set and collation</strong> in a way that <code>mysql_real_escape_string()</code> gets affected. What is the correct way to do that?</p>