Home Indentation and Spacing in MySQL

As with any other language, your choice of spacing for any indentation in MySQL should be consistent. Many developers choose to use 4-space or 2-space indentation. You will also find fewer cases of indentation in MySQL, but we have provided expected course guidelines for formatting MySQL code here.

Indentation for SELECT Queries

For SELECT queries, each clause (e.g. WHERE) should start on a new line to align with the starting SELECT. Lines should never be longer than 100 characters. Break apart long clauses with readability and consistency in mind - a recommended convention is to align similar parts of a clause just after the corresponding clause name or by one level of indentation (see "good example" for the long FROM clause as an example). For the WHERE clauses you may start multi-condition operators (AND, OR) on new lines as long as you are consistent. 

SELECT
DISTINCT c.name
FROM courses c, 
students s1,
students s2, 
grades g1, 
grades g2 
WHERE g1.course_id = c.id AND g1.student_id = s1.id AND g2.course_id = c.id AND g2.student_id =
s2.id AND s1.name = 'Bart' AND s2.name = 'Lisa';

Below are two good alternatives to the previous "bad example" regarding spacing and indentation of the same SELECT query: 

SELECT DISTINCT c.name
FROM courses c, students s1, students s2, grades g1, grades g2
WHERE g1.course_id = c.id AND g1.student_id = s1.id
  AND g2.course_id = c.id AND g2.student_id = s2.id
  AND s1.name = 'Bart' AND s2.name = 'Lisa';

SELECT DISTINCT c.name
FROM courses c, students s1, students s2, grades g1, grades g2
WHERE g1.course_id = c.id
AND g1.student_id = s1.id
AND g2.course_id = c.id
AND g2.student_id = s2.id
AND s1.name = 'Bart'
AND s2.name = 'Lisa';

Indentation for the CREATE TABLE Statement

Indentation for the CREATE TABLE statement should be consistent. When using the CREATE TABLE statement, end your first line with "(" and specify your column names and constraints each on a separate line, indented once within the CREATE body. End each line in the body with a comma (except for the last line). Whether you include a space before the opening "(" is up to you, but you should be consistent. The closing ")" should be on its own line with a semicolon.

If creating multiple tables, your CREATE statements should be separated by a single blank line for readability. 

CREATE TABLE dogbreeds(breed_id INT, breed_name VARCHAR(20), PRIMARY KEY (breed_id));
CREATE TABLE doggies
(
dog_id INT AUTO INCREMENT,
dog_name VARCHAR(20),
dog_age INT,
breed_id INT,
PRIMARY KEY (breed_id)
FOREIGN KEY (breed_id) REFERENCES dobreeds(breed_id));

CREATE TABLE dogbreeds(
  breed_id INT, 
  breed_name VARCHAR(20), 
  PRIMARY KEY (breed_id)
);
 
CREATE TABLE doggies (
  dog_id INT AUTO INCREMENT,
  dog_name VARCHAR(20),
  dog_age INT,
  breed_id INT,
  PRIMARY KEY (dog_id)
  FOREIGN KEY (breed_id) REFERENCES dogbreeds(breed_id)
);

Indentation for INSERT statements

INSERT statements may vary in their length and overall complexity depending on the table and values you are working with. Consistency and readability are most important when indenting your INSERT statements. Limit line lengths to 100 characters (although single line strings might be longer than 100 characters, which is ok).

In the following "good examples", the first example uses a convention to indent the VALUES part of the INSERT statement by one indentation level, starts the first value on the same line, and indents the remaining values each on their own line. The second good example uses a convention to align VALUES with the INSERT statement, similar to how WHERE is aligned with SELECT in SELECT statements. This example also follows a common convention of aligning all values together. The last example is reasonable to fit the value arguments on one line since they are fairly short and maintain readability. 

INSERT INTO todos (task_num, task_name, due_date, task_type)
VALUES (1, "post creative homework on course page",
DATE_ADD(NOW(), INTERVAL 1 HOUR), "CSE154");

INSERT INTO doggies 
(dog_name, dog_age, dog_breed)
VALUES ("Mowgli", 1, 12);

INSERT INTO todos (task_num, task_name, due_date, task_type)
  VALUES (1,
    "post creative homework on course page",
    DATE_ADD(NOW(), INTERVAL 1 HOUR),
    "CSE154");

INSERT INTO todos (task_num, task_name, due_date, task_type)
VALUES (1,
        "post creative homework on course page",
        DATE_ADD(NOW(), INTERVAL 1 HOUR),
        "CSE154");

INSERT INTO todos (task_num, task_name, due_date, task_type)
VALUES (1, "post creative homework on course page",
        DATE_ADD(NOW(), INTERVAL 1 HOUR), "CSE154");

INSERT INTO doggies (dog_name, dog_age, dog_breed)
VALUES ("Mowgli", 1, 12);