From sqlite syntax to mysql syntax

I’m having some troubles moving from sqlite syntax to mysql syntax. Does anybody know a ressource showing the basic syntax in sqlite and its equivalent in mysql ?

In my particular case, I want to transform this sqlite code into mysql one:

DROP TABLE IF EXISTS post;


CREATE TABLE post (
  a INTEGER PRIMARY KEY AUTOINCREMENT,
  b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  c TEXT NOT NULL,
  d TEXT NOT NULL,
  e TEXT NOT NULL,
  f TEXT NOT NULL,
  g TEXT NOT NULL,
  h TEXT NOT NULL,
  i TEXT,
  j TEXT NOT NULL,
  k TEXT,
  l TEXT NOT NULL,
  m TEXT NOT NULL
);

For the moment, I did that:

DROP TABLE IF EXISTS post;

CREATE TABLE post (
  a INT AUTO_INCREMENT PRIMARY KEY,
  b CURRENT_TIMESTAMP(),
  c TEXT NOT NULL,
  d TEXT NOT NULL,
  e TEXT NOT NULL,
  f TEXT NOT NULL,
  g TEXT NOT NULL,
  h TEXT NOT NULL,
  i TEXT,
  j TEXT NOT NULL,
  k TEXT,
  l TEXT NOT NULL,
  m TEXT NOT NULL
);

But I got errors with CURRENT_TIMESTAMP() and TEXT.

Any help would be highly appreciated.

Answer

Actually the only change that you have to do is to use AUTO_INCREMENT instead of SQLite’s AUTOINCREMENT keyword and your statement will work fine in MySql too.
See the demo.

But you’d better not use MySql’s TEXT data type.
SQLite has only 1 data type for storing strings and this is the TEXT data type.
On the other hand MySql supports VARCHAR and CHAR data types for storing string values.
If a column does not hold really long strings, then the preferred data type is VARCHAR and not TEXT.
You can find more about the differences of the 2 data types in these threads:

• VARCHAR vs TEXT in MySQL
• Difference between VARCHAR and TEXT in MySQL

So a statement that would make sense is:

CREATE TABLE post (
  a INTEGER PRIMARY KEY AUTO_INCREMENT,
  b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  c VARCHAR(10) NOT NULL,
  d VARCHAR(10) NOT NULL,
  e VARCHAR(10) NOT NULL,
  f VARCHAR(10) NOT NULL,
  g VARCHAR(10) NOT NULL,
  h VARCHAR(10) NOT NULL,
  i VARCHAR(10),
  j VARCHAR(10) NOT NULL,
  k VARCHAR(10),
  l VARCHAR(10) NOT NULL,
  m VARCHAR(10) NOT NULL
);

You may replace the number 10 in VARCHAR(10) with max expected length for each of the columns.