MySQL versus SQLite

This page compares the different commands needed to do similar tasks using the SQLite and MySQL database engines.

Get schema from database

Use mysqldump with the --no-data option:

mysqldump -u user-name -p --no-data db-name > file-name

See RE: mysqldump ONLY Schema (

Start the command line tool sqlite3 and type .schema.

Insert values into a table

MySQL accepts the same syntax as SQLite. It also has a syntax using SET of the form

INSERT INTO table SET col1=1,col2=2,col3=3
This doesn't work with SQLite.

SQLite only has one syntax:

INSERT INTO table(col1,col2,col3) VALUES (1,2,3)

Create an auto-incremented column
CREATE TABLE `inputs` (
  `input_id` int(11) NOT NULL AUTO_INCREMENT
       input_id INTEGER PRIMARY KEY

Copyright © Ben Bullock 2009-2021. All rights reserved. For comments, questions, and corrections, please email Ben Bullock ( or use the discussion group at Google Groups. News about the site. / Privacy / Disclaimer