21.1.3 En quoi la syntaxe SQL de mSQL 2.0 diffère de MySQL

Column types

MySQL
Has the following additional types (among others; see 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. mysql_insert_id().
  • DEFAULT value for all columns.
mSQL2
mSQL column types correspond to the MySQL types shown below:
mSQL type Corresponding MySQL 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.
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. 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.
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 wildcard 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 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 requêteswhen 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. 6.6 Fonctionnement du système de droits.
mSQL
Has a file `mSQL.acl' in which you can grant read/write privileges for users.