From sqlite syntax to mysql syntax

Question

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.

score 3 · Answer 1 · answered Dec 12 '20 at 21:20

MySQL syntax(translated with https://www.jooq.org/translate/):

drop table if exists post;
create table post (
  a int not null auto_increment,
  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,
  primary key (a)
);

db<>fiddle demo

score 3 · Accepted Answer · answered Dec 12 '20 at 21:48

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:

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.

From sqlite syntax to mysql syntax

2 Answers2