A dump is a list of statements. Most of them fall into a few groups: those that define things, those that carry rows, and those that set up the session. Some lines are not SQL at all, but commands for the client that loads the file.
Definitions
| Statement | What it does in a dump |
|---|---|
CREATE TABLE | Makes a table. See CREATE TABLE. |
DROP TABLE | Removes a table first, so the load can be run over an existing database. Usually DROP TABLE IF EXISTS. |
ALTER TABLE | Adds what the CREATE TABLE left out. PostgreSQL dumps add primary keys and foreign keys this way, and phpMyAdmin adds keys and AUTO_INCREMENT. |
CREATE INDEX | Builds an index on a table. It can come after the data. |
CREATE VIEW | A stored query that reads like a table. |
CREATE TRIGGER | Code that runs when rows change. |
CREATE PROCEDURE, CREATE FUNCTION | Stored routines. Their bodies hold semicolons, which is why the dialects have ways to mark where a routine ends. See Ending statements. |
CREATE SEQUENCE, CREATE TYPE, CREATE SCHEMA | PostgreSQL objects that tables depend on. |
CREATE DATABASE, USE | Make and choose the database. Written only when the tool is asked to include them. |
Rows
| Statement | What it does |
|---|---|
INSERT | Adds rows, one or many at a time. See INSERT. |
REPLACE | MySQL’s form of INSERT that deletes a row with the same key first. |
COPY … FROM stdin | PostgreSQL’s fast load, with the rows as lines after it. See COPY. |
Session settings
These change how the loading session behaves, and do not change the schema.
SET NAMES utf8mb4;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET IDENTITY_INSERT [dbo].[fruit] ON;
PRAGMA foreign_keys=OFF;Two settings change how the text of the file itself is read, so a reader of the file
has to follow them: MySQL’s sql_mode, when it has
NO_BACKSLASH_ESCAPES or ANSI_QUOTES, and PostgreSQL’s
standard_conforming_strings. See
Literals.
Locks and transactions
| Statement | Used for |
|---|---|
LOCK TABLES, UNLOCK TABLES | Written by mysqldump around each table’s rows. |
BEGIN TRANSACTION, COMMIT | Around the whole of a SQLite dump, so the load is one transaction. |
SET FOREIGN_KEY_CHECKS | Turned off during a MySQL load, so tables can be loaded in any order. |
Commands of the client
These are read by the client that runs the file and are never sent to the server.
| Line | Client | Meaning |
|---|---|---|
DELIMITER ;; | mysql | Changes the text that ends a statement. |
\connect mydb | psql | Connects to another database. Any line starting with a backslash is a psql command. |
GO | sqlcmd, Management Studio | Ends a batch of statements. |