[DOC] Unsupported MySQL Data Types

Discussion on a SQL Parser for EasyMOD
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)

[DOC] Unsupported MySQL Data Types

Post by markus_petrux »

Almost all MySQL data types are supported by the SQL Parser. When necessary, each one is converted to the proper form on the target DBMS. However, no support is provided yet for the following MySQL data types:
  • SET
  • ENUM

    Part of the behaviour of these data types could be emulated with CHECK constraints (supported by PostgreSQL and MSSQL). However, if you look at the MySQL reference you'll notice MySQL also accepts integers for this kind of columns, and the worst thing is it sorts these fields using the integers assigned to each one of the options defined.
    • ENUM('value1','value2',...)

      An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented internally as integers.
    • SET('value1','value2',...)

      A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are represented internally as integers.
    Therefore, we could consider ENUM and SET non-portable data types. It would be best to not rely on propietary MySQL features and try to implement these checks in the code instead.

    It is probably easy to convert these data types, in column definitions, the DDL side (which is the scope of the SQL Parser), however the problems might mostly come when using them in the DML side (SELECT, INSERT and UPDATE) as the date/time format may depend on server parameters... hmmm... probably the most reliable solution is to use integers instead.
Support for these data types may or may not be added in the future. It basically depends on finding a reasonable way to properly convert them to other SQL servers. Meanwhile, they are considered non-portable.