GNU Info

(mysql.info)Syntax differences

---

Prev: Protocol differences Up: Compare mSQL

---

Enter node , (file) or (file)node

How `mSQL' 2.0 SQL Syntax Differs from MySQL
............................................

*Column types*

`MySQL'
     Has the following additional types (among others; Note: `CREATE
     TABLE'.):
        * `ENUM' type for one of a set of strings.

        * `SET' type for many of a set of strings.

        * `BIGINT' type for 64-bit integers.

`'
     MySQL also supports the following additional type attributes:
        * `UNSIGNED' option for integer columns.

        * `ZEROFILL' option for integer columns.

        * `AUTO_INCREMENT' option for integer columns that are a
          `PRIMARY KEY'.  Note: `mysql_insert_id()'.

        * `DEFAULT' value for all columns.

`mSQL2'
     `mSQL' column types correspond to the MySQL types shown below:
     `mSQL'     *Corresponding MySQL type*
     *type*     
     `CHAR(len)'`CHAR(len)'
     `TEXT(len)'`TEXT(len)'. `len' is the maximal length.  And `LIKE'
                works.
     `INT'      `INT'. With many more options!
     `REAL'     `REAL'. Or `FLOAT'. Both 4- and 8-byte versions are
                available.
     `UINT'     `INT UNSIGNED'
     `DATE'     `DATE'. Uses ANSI SQL format rather than `mSQL''s own
                format.
     `TIME'     `TIME'
     `MONEY'    `DECIMAL(12,2)'. A fixed-point value with two decimals.

*Index Creation*

`MySQL'
     Indexes may be specified at table creation time with the `CREATE
     TABLE' statement.

`mSQL'
     Indexes must be created after the table has been created, with
     separate `CREATE INDEX' statements.

*To Insert a Unique Identifier into a Table*

`MySQL'
     Use `AUTO_INCREMENT' as a column type specifier.  Note:
     `mysql_insert_id()'.

`mSQL'
     Create a `SEQUENCE' on a table and select the `_seq' column.

*To Obtain a Unique Identifier for a Row*

`MySQL'
     Add a `PRIMARY KEY' or `UNIQUE' key to the table and use this.
     New in Version 3.23.11: If the `PRIMARY' or `UNIQUE' key consists
     of only one column and this is of type integer, one can also refer
     to it as `_rowid'.

`mSQL'
     Use the `_rowid' column. Observe that `_rowid' may change over time
     depending on many factors.

*To Get the Time a Column Was Last Modified*

`MySQL'
     Add a `TIMESTAMP' column to the table. This column is
     automatically set to the current date and time for `INSERT' or
     `UPDATE' statements if you don't give the column a value or if you
     give it a `NULL' value.

`mSQL'
     Use the `_timestamp' column.

*`NULL' Value Comparisons*

`MySQL'
     MySQL follows ANSI SQL, and a comparison with `NULL' is always
     `NULL'.

`mSQL'
     In `mSQL', `NULL = NULL' is TRUE.  You must change `=NULL' to `IS
     NULL' and `<>NULL' to `IS NOT NULL' when porting old code from
     `mSQL' to MySQL.

*String Comparisons*

`MySQL'
     Normally, string comparisons are performed in case-independent
     fashion with the sort order determined by the current character
     set (ISO-8859-1 Latin1 by default).  If you don't like this,
     declare your columns with the `BINARY' attribute, which causes
     comparisons to be done according to the ASCII order used on the
     MySQL server host.

`mSQL'
     All string comparisons are performed in case-sensitive fashion with
     sorting in ASCII order.

*Case-insensitive Searching*

`MySQL'
     `LIKE' is a case-insensitive or case-sensitive operator, depending
     on the columns involved. If possible, MySQL uses indexes if the
     `LIKE' argument doesn't start with a wild-card character.

`mSQL'
     Use `CLIKE'.

*Handling of Trailing Spaces*

`MySQL'
     Strips all spaces at the end of `CHAR' and `VARCHAR' columns. Use
     a `TEXT' column if this behavior is not desired.

`mSQL'
     Retains trailing space.

*`WHERE' Clauses*

`MySQL'
     MySQL correctly prioritizes everything (`AND' is evaluated before
     `OR'). To get `mSQL' behavior in MySQL, use parentheses (as shown
     in an example below).

`mSQL'
     Evaluates everything from left to right. This means that some
     logical calculations with more than three arguments cannot be
     expressed in any way. It also means you must change some queries
     when you upgrade to MySQL. You do this easily by adding
     parentheses.  Suppose you have the following `mSQL' query:
          mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;
     To make MySQL evaluate this the way that `mSQL' would, you must
     add parentheses:
          mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));

*Access Control*

`MySQL'
     Has tables to store grant (permission) options per user, host, and
     database. Note: Privileges.

`mSQL'
     Has a file `mSQL.acl' in which you can grant read/write privileges
     for users.

`'