1.10.1.3 How mSQL 2.0 SQL Syntax Differs from MySQL

Column types

MySQL Server

Has the following additional types (among others; see section 6.5.3 CREATE TABLE Syntax):

• 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 Server also supports the following additional type attributes:

• UNSIGNED option for integer and floating-point columns.
• ZEROFILL option for integer columns.
• AUTO_INCREMENT option for integer columns that are a PRIMARY KEY. See section 9.1.3.31 mysql_insert_id().
• DEFAULT value for all columns.

mSQL2

mSQL column types correspond to the MySQL types shown in the following table:

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 SQL-99 format rather than mSQL's own format.
TIME	TIME
MONEY	DECIMAL(12,2). A fixed-point value with two decimals.

Index Creation

MySQL Server

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 Server

Use AUTO_INCREMENT as a column type specifier. See section 9.1.3.31 mysql_insert_id().

mSQL

Create a SEQUENCE on a table and select the _seq column.

To Obtain a Unique Identifier for a Row

MySQL Server

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 Server

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 Server

MySQL Server complies with standard SQL, and a comparison with NULL is always UNKNOWN.

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 Server.

String Comparisons

MySQL Server

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 Server

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 Server

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

mSQL

Retains trailing space.

WHERE Clauses

MySQL Server

MySQL correctly prioritises everything (AND is evaluated before OR). To get mSQL behaviour in MySQL Server, use parentheses (as shown in an example later in this section).

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 Server. 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 Server 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 Server

Has tables to store grant (permission) options per user, host, and database. See section 4.2.6 How the Privilege System Works.

mSQL

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

User Comments

Add your own comment.