40

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?

Malwinder Singh
  • 6,644
  • 14
  • 65
  • 103

2 Answers2

36

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.

bohica
  • 5,932
  • 3
  • 23
  • 28
5

As you may know Ansi and unicode are applied to the data character type. You can choose one or the other, it is not a question of processor Here is the difference between them: - Unicode allow you to have all the characters properties enabled but reducing raw speed treatment - Ansi, the opposite, less character type but raw speed treatement will speed up

If you are a new user take the Unicode one, if not you can choose the Ansi one.

I hope you've got the point ;)

Carnangel
  • 81
  • 8
  • what is raw speed treatment? – Malwinder Singh Sep 17 '14 at 12:08
  • 1
    Basically it is the time of reading datas, and by so the appearing time of datas . Assume that you've got 2 projects : One in which you ve got two tables with relatively few data The other in which there are 50 tables and a lot of datas If in either cases you do "Select * from 'table'" -the first one will read the data really quick (high raw speed) - The second will take a greater amount of time to read the table data's (less raw speed) – Carnangel Sep 17 '14 at 12:14