SQL

Tables names:

BOOLEAN is TINYINT. See MySQL :: MySQL 5.7 Reference Manual :: 11.1.1 Numeric Type Overview

MySQL unique keys can be NULL whereas primary keys cannot be There can be only one Primary key in a table but one or more unique keys

Actions when a table is modified:

Syntax

Race condition

Aka concurrency

Use single atomic statement (like INNER JOIN) or transaction

Game Leaderboard

For performances, you can create temporary/virtual table CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1). It's unique per session (this is not supported by non CGI PHP). Else use a regular table. See also views

-- insert game
INSERT INTO games (time, player, ip, email, mobile, over_reason, round, beginning, score)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
-- get rank
SELECT (SELECT 1 + COUNT(*) FROM games WHERE round = ? AND score > ?) as rank;
-- get top 10
SELECT player, score FROM games
WHERE round = ?
ORDER BY score DESC
LIMIT 10;

  1. game end timestamp

  2. player's ID

  3. player's ip (see Get client IP)

  4. player's email

  5. player's mobile

  6. game over reason

  7. game's round

  8. game beginning timestamp

  9. game score

  10. game's round

  11. game's score

  12. game's round

leaderboards are read-heavy. Favor memory to store fields used to compute rank

Row number

Insert new lines

INSERT INTO test VALUES(replace('a\nb\nc\nd\ne', '\n', char(10)));

Insert in multiple tables, use transaction to rollbak in case of error (conflict, database connection lost, etc.)

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage)
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;
INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19

Import file:

Column size

Variables

SET @myvar = 10
SELECT @myvar := 10
UPDATE mytable SET col = @tmp := col, col = 10

Prepare statement

Aka SQL injection protection, supported natively by the server.

Doesn't support column name nor value. For latest, user variable can be used (eg. SET @var1 = 1, @var2 = 2), see also stored procedure - Set variable with dynamic name in trigger (MySQL) - Database Administrator Stack Exchange Often doesn't support multiple statements (prepare individualy), or use procedure

Optimisation

Indexes:

A clustered index is like the contents of a phone book. You can open the book at 'Hilditch, David' and find all the information for all of the 'Hilditch's right next to each other. Here the keys for the clustered index are (lastname, firstname).

Redundant data vs join tables (foreign keys):

Insert lot of rows:

./mysqltuner.pl --user root --pass root --defaults-file .../my.cnf --mysqladmin .../mysqladmin --mysqlcmd .../mysql
./mysqltuner.pl --user root --pass root --defaults-file /Applications/MAMP/conf/my.cnf --mysqladmin /Applications/MAMP/Library/bin/mysqladmin --mysqlcmd /Applications/MAMP/Library/bin/mysql

Compress data

Use COMPRESS() in VARBINARY or BLOB: 4-byte length + zlib data (zlib header + deflate data)

Errors

Transaction : commit or rollback

Use SQLSTATE codes in your client, it's more standardized.

Clear a table

# Kick, internaly delete and recreate the table
TRUNCATE TABLE table;
# Delete each rows, but doesn't reset auto increment
DELETE FROM table;

Truncate a table with referenced fields as foreign keys

DELETE FROM table;
-- will execute cascades
ALTER TABLE table AUTO_INCREMENT = 1;

String comparison

Aka like

-- select URL contains url encoded space
SELECT 'http://example/David%20and%20Goliath' LIKE '\%20'

Row match vs affected

Time

Enum

FIND_IN_SET and ENUM

If number of values in set change, the col size will be recomputed

Or:

status can contains one or more (bits flags):

1 = Prospective Customer 2 = Cancelled Service 4 = Pool Service 8 = lawn mowing 16 = window washing 32 = tree trimming 64 = house painting 128 = mobile oil change

All of window washing customers even if they are cancelled:

SELECT * FROM customers WHERE (status & 16) AND !(status & 2)

All of tree trimming AND pool service customers i.e. 32+4:

SELECT * FROM customers WHERE status & 36

Concatenate list as string

SET SESSION group_concat_max_len = 1000000;#max 1MB
SELECT GROUP_CONCAT(k SEPARATOR ',') FROM (SELECT CONCAT(col1, ':', col2) AS k FROM sometable WHERE condition) AS tmp

JSON with SQLite

Merge data

PreviousSQLNextDigitization

Last updated