What are the differences in the SQL syntax between MySQL & mSQL 2.0?

• CREATE TABLE

  MySQL

  Has (among others) the following extra types: See section CREATE TABLE syntax.

  • UNSIGNED option for all integer columns.
  • ZEROFILL option for all integer columns.
  • AUTO_INCREMENT option for all integer columns that also is a PRIMARY KEY. See section How can I get the unique ID for the last inserted row?.
  • DEFAULT value for all columns.
  • ENUM type for one of a set of strings.
  • SET type for many of a set of string.
  • BIGINT type for 64 bit integers.

  mSQL2

  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 bytes versions are available.
  uint	Unsigned integer.
  date	date. Takes ANSI SQL format instead of mSQLs own.
  time	time
  money	decimal(12,2). A fixed point value with two decimals.

• To create indexes.

  MySQL

  All indexes have to be given to CREATE TABLE. Indexes can not be removed without recreating the table. See section ALTER TABLE syntax.

  mSQL

  Indexes must be created by a separate CREATE INDEX clause. Indexes may be removed with DROP INDEX.

• To get a unique identifier in a table at insert.

  MySQL

  Use AUTO_INCREMENT as a column type specifier. See section How can I get the unique ID for the last inserted row?.

  mSQL

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

• To get a unique identifier for a row.

  MySQL

  Add a PRIMARY KEY to the table.

  mSQL

  Use the _rowid column. Observe that _rowid may change depending on many factors.

• To get the time a column was last modified.

  MySQL

  Add a TIMESTAMP column to the table. This column will automaticly be updated to the current time if you don't give the column a value or if you give it a NULL value in a UPDATE or INSERT statement.

  mSQL

  Use the _timestamp column.

• In mSQL NULL = NULL is TRUE. MySQL follows ANSI SQL and a comparison with NULL is always NULL. One has to change =NULL to IS NULL when porting old code from mSQL to MySQL.
• All string comparisons are by default case independent with case according to ISO-8859-1 Latin1. If you don't like this you should declare your columns with the BINARY attribute.
• Case insensitive searching.

  MySQL

  LIKE is case insensitive or case sensitive depending on the used columns. If possible MySQL uses indexes if the like argument doesn't start with a wildcard.

  mSQL

  Use CLIKE.

• End space handling.

  MySQL

  Stripps all spaces att the end of CHAR and VARCHAR columns. Currently use a TEXT column if this behavior is undesired.

  mSQL

  Keeps end space.

• What are the differences in the WHERE statement?

  MySQL

  MySQL does everything correctly prioritized (AND is evaluated before OR). To get mSQL behaviour in MySQL, use parenthesis: select * from table where a=1 and b=2 or a=3 and b=4 -> select * from table where (a=1 and (b=2 or (a=3 and (b=4)))).

  mSQL

  Evaluates everything from left to right. That means that some logical calculations with more that 3 arguments can not be expressed in any way. This also mean that you have to change some queries when you upgrade to MySQL. You do this easy by adding parenthesis. Always when you are in doubt about what a query does:

  1. Remove all distinct keywords to make the query easier to debug.
  2. Write all columns from all tables to really see what you are doing.

  This will make it easier to see what goes wrong.

• Access Control

  MySQL

  Has tables to store grant options per user, host and db. See section How does the privilege system work?

  mSQL

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

Go to the first, previous, next, last section, table of contents.