Ability to set collation on connection

[prencher]

By default, you set utf8_general_ci. However, for better language support, utf8_unicode_ci is more appropriate. It would be nice if you could specify the collation along with the charset.

[arnehormann]

By default, we don't set any collation. The MySQL Handshake supports setting a chracter set, but not a collation.
You have to set the collation yourself.
You can set it by appending collation_connection=utf8_unicode_ci to the DSN, wich executes SET collation_connection=utf8_unicode_ci as the first query when a new connection is established.

[julienschmidt]

Oh yes, the driver sets a collation

[julienschmidt]

I must admit that I have no Idea what this actually does...
In the protocol specification it is called "Character Set" but the value must be the name of a collation. See: http://dev.mysql.com/doc/internals/en/character-set.html#packet-Protocol::CharacterSet

I assume that the collation also determines the character set used. For example utf8_general_ci also sets the character set to UTF-8.

[xaprb]

Just going from memory. A character set has a default collation. If you
don't specify one, the default will be used. If you specify a collation, it
automatically selects the character set to which it belongs. I could be
wrong about some or all of this.

[arnehormann]

We could fix this with an exported var in the driver:
var Collation uint8 = utf8_general_ci
if we use that in the handshake, it can be set for all new connections if the driver is imported directly (like for the LOAD DATA LOCAL INFILE support).
On the plus side, it won't change anything for regular users not using this feature.

But it will need some documentation to clarify that setting it to a different collation does not change existing connections (which is hard to reason about because of database/sql's pooling). It would only really make sense to set it before first calling Open. And that you get the supported collations with SHOW collation (column id). And that there's no reasonable way to support error handling if an unavailable value is set. And that reading the variable is useless - it's not changed when a collation is unavailable and may not match the collation used in a specific connection.

I think that's viable and desirable. What do you think, Julien?