Difference between ANSI and Unicode drivers of MySQL

Question

On choosing Data Source under ODBC (64-Bit) on Windows, i get two available options of MySQL Database:

• MySQL ODBC 5.3 ANSI Driver
• MySQL ODBC 5.3 Unicode Driver

What are the difference between these two?

Answer 1

Firstly I should say that I don't use MySQL but I do know about ODBC Drivers. In ODBC there are different APIs for unicode and ansi. The ansi APIs end in A and the unicode APIs end in W (e.g., SQLPrepareA and SQLPrepareW). The ansi APIs accept bytes/octets for character strings and hence can only handle chrs 0-255. The unicode APIs accept SQLWCHARs which are 2 byte UCS-2 encoded unicode codepoints (newer MS SQL Server versions can handle UTF16 encoded strings) and so can handle approximately the first 65000 codepoints in unicode.

So if you need to store unicode data you have no choice which driver to use.

I would not let the comments on speed from Carnangel put you off using the unicode driver and in any case his comments do not include any facts. He may be referring to:

If you store unicode data in MySQL it will be UTF-8 encoded and transferred over your network as UTF-8. At the client end the ODBC driver will have to convert the UTF-8 encoded data into UCS-2 as this is what ODBC needs. Obviously the reverse applies.

If you write an ANSI ODBC application (that is one which uses the ansi ODBC apis) with a unicode ODBC driver then the ODBC Driver manager will have to convert the UCS-2 the driver returns to 8 bit (lossy) and convert the 8 bit data you pass to the driver to UCS-2. So don't do that.

These days I'd be surprised if anyone is still using ANSI ODBC drivers.