MySQL versus SQLite

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

MySQL SQLite
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 (lists.mysql.com).

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
);

CREATE TABLE inputs (
       input_id INTEGER PRIMARY KEY
);
Copyright © Ben Bullock 2009-2023. All rights reserved. For comments, questions, and corrections, please email Ben Bullock (benkasminbullock@gmail.com) or use the discussion group at Google Groups. / Privacy / Disclaimer