All notes
Mysql

Basics

Installation

CentOS


sudo service mysqld start
sudo chkconfig mysqld on

# Remember: it only works when root has not set password yet.
# Set a password for the MySQL root USER.
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h img-server03.l password 'new-password'

# Alternatively you can run:
# which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.
/usr/bin/mysql_secure_installation

MacOS


# Add /usr/local/mysql/bin to PATH.
echo "export PATH=$PATH:/usr/local/mysql/bin" | sudo tee > /etc/profile
source /etc/profile

sudo bin/mysqld_safe &
# Next command is optional
cp support-files/mysql.server /etc/init.d/mysql.server

File './mysql-bin.index' not found

You can find "mysql-bin.index" under "/usr/local/var/mysql/".


sudo chown -R mysql /usr/local/var/mysql/

Logs

dev.mysql.com: log.

Possible destinations for log entries are log files or the general_log and slow_log tables in the mysql database.


SHOW CREATE TABLE mysql.general_log;
SHOW CREATE TABLE mysql.slow_log;

To disable logging so that you can alter (or drop) a log table, you can use the following strategy:


SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

CRUD

Create, retrieve, update and delete (CRUD).

Comments

SQL (Structured Query Language) has three types of comments: '#', "-- ", and c-style multi-line "/* */".

Foreign keys

Delete cascade

References

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!


/* Buildings contains rooms.*/

CREATE TABLE buildings (
  building_no int(11) NOT NULL AUTO_INCREMENT,
  building_name varchar(255) NOT NULL,
  address varchar(355) NOT NULL,
  PRIMARY KEY (building_no)
) ENGINE=InnoDB;

CREATE TABLE rooms (
  room_no int(11) NOT NULL AUTO_INCREMENT,
  room_name varchar(255) NOT NULL,
  building_no int(11) NOT NULL,
  PRIMARY KEY (room_no),
  KEY building_no (building_no),
  CONSTRAINT rooms_ibfk_1 
  FOREIGN KEY (building_no) 
  REFERENCES buildings (building_no) 
  ON DELETE CASCADE
) ENGINE=InnoDB;

# Find tables that associated with the buildings table with the CASCADE delete rule in the classicmodels database:

USE information_schema;
 
SELECT table_name
FROM referential_constraints
WHERE constraint_schema = 'classicmodels' AND
      referenced_table_name = 'buildings' AND
      delete_rule = 'CASCADE'

charset

MySQL.


/* SET NAMES indicates what character set the client will use to communicate with the server. */
SET NAMES 'charset_name' [COLLATE 'collation_name']

/* See database charset. */
show variables like "character_set_database";

/* See table charset. */
show table status

Configs

Auto completion

http://stackoverflow.com/questions/14070156/mysql-tab-completion-not-working.

On mysql prompt, rehash does the trick.

in my.cnf file, add

[mysql]
auto-rehash

add no-auto-rehash to disable auto completion.

Command line

mysqladmin

StackOverflow. mysqladmin, like other MySQL-related command line tools (mysql, mysqldump, mysqlshow etc.), offers options to provide such access data.

h: Which host to connect to. If not provided, localhost is assumed
u: Which user to connect as. If not provided, root is assumed
p: Which password to use. If not provided, no password is used

# Create database.
mysqladmin -p create mailserver
# Login directly to the database.
mysql -p mailserver

mysqladmin create db_name
mysqladmin drop db_name
mysqladmin password new-password

# Check whether the server is available.
mysqladmin ping

# Show all vars
mysqladmin variables

mysqldump

TheGeekStuff.


# backup:
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
# backup bugs and sugarcrm databases.
mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql
# Backup all the databases:
mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql
# Backup only the accounts_contacts table from sugarcrm database:
mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql

# Restore a database:
mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

mysql


mysql db_name < script.sql > output.tab

# -s, --silent, avoid tabular and escaping.
# -N, --skip-column-names.
# -p and the pswd should be concated without any space in between.
# -e execute commands.
patid=`mysql -u root -p$rootPwd -e "select patID from patient order by accessed_time limit 1;" -sN medImg`
echo $patid

Interactive commands

dev.mysql.com: mysql commands.

system

system command, \! command: Execute the given command using your default command interpreter. For example:


system w
/* 00:08:21 up 103 days, 13:55,  3 users,  load average: 117.01, 96.42, 82.98 */

The system command works only in Unix.

mysql.server

It's a script under /usr/local/bin.


sudo mysql.server start

mysqld

Use mysql.server instead, which calls mysqld_safe.

mysql_install_db

mysql.com: mysql-install-db.

It's deprecated. Should use instead "mysqld --initialize or --initialize-insecure". It initializes the MySQL data directory and creates the system tables that it contains.

It generates a random administative password, and writes the password to a file "$HOME/.mysql_secret". After resetting the password, remove the .mysql_secret file.

Scripts

Include, source


DROP TABLE foo;

# To include another script
source bar.mysql

/* Call system command ls. */ 
system ls;
system pwd;

/* Pity: we can't use tab to do completion here. */

Message

Sometimes you may want your script to display progress information to the user. For this you can insert statements like this:


SELECT 'Finished creating table 1.' AS ' ';

Nomenclature

Datatypes

Intro

Look up fast in w3c page.

DATETIME vs TIMESTAMP

Even if DATETIME and TIMESTAMP return the same format, they work very differently.

TEXT and VARCHAR

StackOverflow. On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between.

BLOB, TEXT

Reference.

NULL


SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

/* Cannot use arithmetic comparison operators. */
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

/* 0 and '' is not NULL. */
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

insert into table1 values (5, DEFAULT, 10, DEFAULT);

Strings

The strings are enclosed by either '' or "". You can specify the charset: [_charset_name]'string' [COLLATE collation_name].


SELECT _utf8'some text' collate utf8_general_ci;

/* Strings automatically contatenate. The following lines are equivalent: */
'a string'
"a string"
'a' ' ' 'string'

There are several ways to include quote characters within a string:


SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
/* " */

SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

SELECT 'disappearing\ backslash \% \_';
+----------------------------+
| disappearing backslash % _ |
+----------------------------+

cast between string and int

dev.mysql.com: cast functions.



// To cast a string to a number, you normally need do nothing other than use the string value in numeric context:
SELECT 1+'1';
// -> 2

// That is also true for hexadecimal and bit literals, which are binary strings by default:
SELECT X'41', X'41'+0;
// -> 'A', 65
SELECT b'1100001', b'1100001'+0;
//  -> 'a', 97

// A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.

// A number used in string context is converted to a string:
SELECT CONCAT('hello you ',2);
// -> 'hello you 2'

Functions

MySQLDev: string functions.

Quote

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement.

NULL is returned as is.


SELECT QUOTE('Don\'t!');
  /* 'Don\'t!' */
SELECT QUOTE(NULL);
  /* NULL */

Collation

Wikipedia: collation. Collation is the assembly of written information into a standard order. Many systems of collation are based on numerical order or alphabetical order, or extensions and combinations thereof.

Difference between '' and ""

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.

StackOverflow: single quotes or double?. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

Specified key was too long; max key length is 767 bytes

How is char length counted?

In short: Version 4 counts bytes, Version 5 counts characters.

MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to CHAR, VARCHAR, and the TEXT types.
Mysql assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character, since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.

For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.

To solve the problem, change CHARSET of the complaining index field to "latin1".

Why 767?
  1. For a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes.
  2. Storage for variable-length columns includes length bytes. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
Maximum row size
The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

Enums

Reference. Enums are string objects. They save storage and at the same time make queries and output readable.


CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

Enum sorting

ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.

To prevent unexpected results when using the ORDER BY clause on an ENUM column, make sure that the column is sorted lexically rather than by index number by coding ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col).

Database


CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_namea
    [create_specification] ...

/* collation is used for ordering. */
create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

See reference. Database characteristics are stored in the db.opt file in the database directory. In SQL, database is like a directory, and table is like a file.

Manipulate database


show databases;

create database redmine character set utf8;
create user redmine identified by ''; # Set password at next step.
set password for redmine = PASSWORD('mypass'); # Set password, the mypass is encrypted by PASSWORD.
grant all privileges on redmine.* to redmine;
flush privileges; # Update.

/* Read-only. */
grant select on redmine.* to redmine;

drop schema databaseName;
How did I find the database directory

Using select inst_attrs into dumpfile '/home/wangcf/meta.raw' from instance limit 1; to dump the DICOM meta from DCM4CHEE mysql database, but I came across ErrorCode 13. perror 13 in BASH told me that I have no permission to write there.
OK, let's try to dump to PWD. select inst_attrs into dumpfile 'meta.raw' from instance limit 1;, and it succeeded!
Wait a minute, where is the meta.raw saved actually? find / -name "meta.raw" told me that it locates in /var/lib/mysql/pacsdb.
pacsdb is the database I created for DCM4CHEE. In the directory with same name, there are those frm files, MYD files and MYI files. Database=directory, Table=file, OK, seeing is believing!

Table

Reference from w3c.

create table


/* wcf Note: CHARACTER SET must strictly follow varchar. So,
    `name` varchar(128) NOT NULL CHARACTER SET utf8,
will cause error.
*/
create table if not exists tableName (
    `name` varchar(128) CHARACTER SET utf8 NOT NULL,
    sex varchar(8) CHARACTER SET utf8,
    _id int(16) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (_id)
) ENGINE=InnoDB;

CREATE TABLE if not exists Persons
(
	P_Id int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (P_Id)
)

create table if not exists `Patient_BaseInfo`
(
	`0010,0020` char(64) primary key comment 'Patient ID',
	`0010,0010` char(64) default null comment 'Patients Name',
	`0010,0030` char(8)  default null comment 'Patients Birth Date'
) engine=InnoDB;

/* The following set one primary key pk_PersonID, which is made up of
	two columns. */
/* The NOT NULL constraint enforces a column to NOT accept NULL values. */
CREATE TABLE Persons
(
	P_Id int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Create temporary table from select result: (Reference)


CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

Show info about table



SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Copy table

SO: duplicating a MySQL table.


/* Copy the data and the structure, but not the indexes */
CREATE TABLE {new_table} SELECT * FROM {old_table};
/* Or */
CREATE TABLE tbl_new AS SELECT * FROM tbl_old;
/* Copy the structure and indexes, but not the data */
CREATE TABLE {new_table} LIKE {old_table};

/* Copy with indexes and triggers */
CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

FAQ

Error at fieldname/tablename

MySQLDev: keywords.

When using the reserved words as field name or table name, you have to quote like this: `name`.

character set


CREATE TABLE t1 ( ... )
    CHARACTER SET latin1 COLLATE latin1_danish_ci;

Derived table

Fiddle.


CREATE TABLE `mytbl` (
  `id` int,
  `Name` varchar(10),
  `Value` int
);

INSERT INTO `mytbl` (`id`, `name`,`value`) VALUES
(1,'A', '4'),
(1,'A', '5'),
(1,'B', '8'),
(2,'C', '9');

select id, group_concat(`Name` separator ',') as `Column`
from
(
  select id, concat(`Name`, ':',
  group_concat(`Value` separator ',')) as `Name`
  from mytbl
  group by id, `Name`
) tbl
group by id;

This page tells the difference between a derived table and a subquery.

The identifiers

Reference. The identifier quote character is the backtick ("`"):


/* The `select` uses a reserved name for identifier, and needs to be quoted. */
SELECT * FROM `select` WHERE `select`.id > 100;

/* Create a table a`b, having a column named c"d. */
CREATE TABLE `a``b` (`c"d` INT);

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:


CREATE TABLE "test" (col INT);
/* ERROR 1064: You have an error in your SQL syntax... */
SET sql_mode='ANSI_QUOTES';
CREATE TABLE "test" (col INT);
/* Query OK, 0 rows affected (0.00 sec)*/

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.

Constraints

Reference.

To reset the auto_increment to start from 1:


ALTER TABLE tablename AUTO_INCREMENT = 1;

/* Or use the following, set auto_increment to max+1: */
/* Source: stackoverflow. */
--DROP PROCEDURE IF EXISTS ...
CREATE PROCEDURE reset_xyz_autoincrement
BEGIN

  SELECT @max := MAX(ID)+ 1 FROM ABC; 

  PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?'
  EXECUTE stmt USING @max 

  DEALLOCATE PREPARE stmt;

END $$

See http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql.


CREATE TABLE Orders
(
	O_Id int NOT NULL PRIMARY KEY,
	OrderNo int NOT NULL,
	P_Id int FOREIGN KEY REFERENCES Persons(P_Id),
	OrderDate date DEFAULT GETDATE(), /* Use the function GETDATE(). */
	CHECK (O_Id>0)
)

/* To allow naming of a CHECK constraint, and for defining a CHECK
	constraint on multiple columns, use the following SQL syntax:
*/
CREATE TABLE Persons
(
	P_Id int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

Primary keys

Candidate key and Super key

sqlWithManoj.com.

Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. Also known as a minimal Super key.
Primary Key: are the Candidate key attribute/columns that you choose as Primary key column.
Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.
Super Key: is a superset of Candidate key.

unique keys

unique key vs primary key

SO: difference between primary key and unique key.

Unique Key (UK), Primary Key (PK)
PK creates a Clustered index and UK creates a Non Clustered Index.
PK is not null, but UK allows nulls.
There can only be one and only one PK on a table, but there can be multiple UK's.
Why unique keys allow NULL

quora.com.

ANSI SQL allows columns with UNIQUE constraints to have multiple NULLs. These NULLs don't count against the uniqueness of the column, because NULL is not equal to another NULL.

But Microsoft SQL Server implemented this constraint to allow only a single NULL, as though NULL is just another value, and NULL=NULL is true. Why? It's probably a side-effect of the way they implemented the comparison for uniqueness.

Indexes

Indexes allow the database application to find data fast; without reading the whole table. Note that since updating databases needs re-indexing, the indexes are more useful for those databases having frequent search queries.

The indexes are invisible to the users. W3Schools reference.


create index index_name on table_name (column_name);

/* Creates a unique index on a table. Duplicate values are not allowed: */
CREATE UNIQUE INDEX index_name ON table_name (column_name);

In DCM4CHEE mysql database, they name indicies the same as their corresponding columns. This dicussion in stackoverflow says it is OK in MySQL, but a prefix like "ind_" for indicies, "cst_" for constraints are recommended but not reinforced.

Reference. The B-Tree allows for lessThan, greaterThan comparisons in addition to equality comparison, while Hash permits only the comparison for equality. In the case of eqality comparison, Hash is faster than B-Tree. No pain, no gain.

The B-Tree index can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. A search using col_name IS NULL employs indexes if col_name is indexed. If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

unique key cannot have same name as index

The following code will cause error: duplicate key for "pat_id". Renaming the "INDEX pat_id" to "INDEX pat_idIdx" solves the error.


/* Note: the following codes have error in it. */
CREATE TABLE patient (
	pk                BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	pat_id            VARCHAR(250) BINARY unique Not NULL
) ENGINE=INNODB;
CREATE INDEX pat_id ON patient(pat_id(64), pat_id_issuer(64));

View

http://www.w3schools.com/sql/sql_view.asp.


/* Create view. */
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

/* We can query the view above as follows: */
SELECT * FROM view_name

/* SQL CREATE OR REPLACE VIEW Syntax */
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

/* Drop view. */
DROP VIEW view_name

Stored Procedures

MySQL DOC on stored procedure. MySQL DOC on CREATE PROCEDURE and CREATE FUNCTION.


/* Procedure. */
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
	SELECT COUNT(*) INTO param1 FROM t;
END//
delimiter ;

CALL simpleproc(@a);
SELECT @a;

/* Function. */
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');

Trigger

MySQL.


CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

/* Creates a trigger named ins_sum that is associated with the account table.
	The trigger activates before each row inserted into the table.
	INSERT operations cause trigger activation. You can also create triggers for DELETE and UPDATE operations.
*/
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

/* To test the trigger */
SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';

/* Drop the trigger. */
DROP TRIGGER test.ins_sum;

BEGIN...END


delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
	FOR EACH ROW
	BEGIN
		IF NEW.amount < 0 THEN
			SET NEW.amount = 0;
		ELSEIF NEW.amount > 100 THEN
			SET NEW.amount = 100;
		END IF;
	END;//
delimiter ;

Transaction

These statements provide control over use of transactions:

START TRANSACTION or BEGIN start a new transaction.
COMMIT commits the current transaction, making its changes permanent.
ROLLBACK rolls back the current transaction, canceling its changes.
SET autocommit disables or enables the default autocommit mode for the current session.

Partitioning

Types

Partition by (dev.mysql.com: partitioning types):

RANGE: based on column values falling within a given range.
LIST: columns are matched to one of a set of discrete values.
HASH: the whole row is hashed by user-specified expression.
KEY: like HASH, but only one or more columns is evaluated, and the MySQL server provides its own hashing function.
LINEAR HASH: utilizes a linear powers-of-two algorithm instead of the modulus. linear hash.

Best practices

percona.com: mysql partitioning.

Find a natural partition key. Ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather, and queries ALL partitions in a UNION that is not concurrent.

Add the partition key into the primary key along with the auto increment, i.e., PRIMARY KEY (part_id,id). If you don’t have well-designed and small columns for this composite primary key, it could enlarge all of your secondary indexes.

You can partition by range or hash. Range is great because you have groups of known IDs in each table, and it helps when querying across partition IDs. This still can create hotspots in the newest partition, as all new inserts go there. Partitioning by hash “load balances” the table, and allows you to write to partitions more concurrently. This makes range queries on the partition key a bad idea.

Partition in MySQL

Partition by range



---------- Select from a specified partition:
-- https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html
EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G

-- https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
SELECT * FROM employees PARTITION (p1);
SELECT * FROM employees PARTITION (p0, p2);
INSERT INTO employees_copy
  SELECT * FROM employees PARTITION (p2);

---------- Add/drop/discard/... partitions
-- https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

-- PARTITION BY
-- the PARTITION BY or REMOVE PARTITIONING clause must be specified last after any other specifications.
CREATE TABLE pt LIKE t;
ALTER TABLE pt PARTITION BY RANGE (year_col) (
  PARTITION pOld VALUES LESS THAN (1991),
  PARTITION p1995 VALUES LESS THAN (1995),
  PARTITION p1999 VALUES LESS THAN (1999),
  PARTITION p2002 VALUES LESS THAN (2002),
  PARTITION p2006 VALUES LESS THAN (2006),
  PARTITION pNow VALUES LESS THAN MAXVALUE
);

-- DROP/ADD PARTITION
ALTER TABLE tr DROP PARTITION p3;
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
-- With tables that are partitioned by range, you can use ADD PARTITION to add new partitions to the high end of the partitions list only. Trying to add a new partition in this manner between or before existing partitions results in an error: ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition. You can use 'REORGANIZE' instead.

-- REORGANIZE
CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);
ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

-- Create partition on existing table
-- this will actually create the new partitioned table first, then copy over all the existing data, and finally drop the old unpartitioned table.
-- https://forums.mysql.com/read.php?106,264106,264110

-- Add partition
CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);
-- You can add a new partition p3 to this table for storing values less than 2002 as follows:
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
-- wcfNote: new partition can only be appended in the end of partition list, am I right?

-- Drop partition
ALTER TABLE t1 DROP PARTITION p0, p1;

-- https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
-- ALGORITHM=2 means to use key-hashing functions in MySQL 5.5 and later. ALGORITHM=1 uses the old one.
ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;

-- Delete data from partition
-- To delete all rows from partition p0:
ALTER TABLE t1 TRUNCATE PARTITION p0;

-- Coalesce partitions: shrink partition number
CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;
-- To reduce the number of partitions used by t2 from 6 to 4, use the following statement:
ALTER TABLE t2 COALESCE PARTITION 2;
-- Partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3).

-- Remove partitioning
ALTER TABLE t REMOVE PARTITIONING;


---------- Exchange partition
-- https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

-- NOTE: The two tables must be alike.
ALTER TABLE pt
  EXCHANGE PARTITION p
  WITH TABLE nt;

Partition by list

Unlike the case with RANGE partitioning, there is no “catch-all” such as MAXVALUE; all expected values for the partitioning expression should be covered. dev.mysql.com: partitioning list.

Partition keys and unique keys

dev.mysql.com: partitioning limitations.

Error: "A primary must include all columns in the table's partitioning location".

Explanation: Every unique key on the table must use all partitioning keys. This also includes the table's primary key, since it is by definition a unique key.


CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    -- UNIQUE KEY (col1, col2) -- This will not work, because it doesn't include `col3`.
    UNIQUE KEY (col1, col2, col3) -- This works.
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    -- UNIQUE KEY (col1),
    -- UNIQUE KEY (col3)
    UNIQUE KEY (col1, col3) -- This works.
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

-- To add a column to unique constraint:
alter table t drop index index_on_uuid, add constraint index_on_uuid unique (`uuid`(23),`id`);

How MySQL Partitioning Handles NULL

dev.mysql.com: partitioning handling nulls.

MySQL's partitioning implementation treats NULL as being less than any non-NULL value, just as ORDER BY does.

This means records with null will be saved to the first partition (if partitioned by range).

Subpartition

Subpartitions must use HASH or KEY partitioning. Only RANGE and LIST partitions may be subpartitioned; HASH and KEY partitions cannot be subpartitioned. dev.mysql.com: partitioning limitations.



-- https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html
CREATE TABLE ts (id INT, purchased DATE)
    ENGINE = MYISAM
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2'
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    );

Partition in Oracle

oracle-base.com: partitioning an existing table using exchange-partition.

1. Create and populate a large table: big_table.
1. Create a partitioned table like big_table: big_table2.
1. Exchange partition by:
  ALTER TABLE big_table2
  EXCHANGE PARTITION big_table_2007
  WITH TABLE big_table
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;
1. DROP TABLE big_table;
1. RENAME big_table2 TO big_table;

1. Split partition by:
  ALTER TABLE big_table
    SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
    INTO (PARTITION big_table_2005,
          PARTITION big_table_2007)
    UPDATE GLOBAL INDEXES;
  
  ALTER TABLE big_table
    SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
    INTO (PARTITION big_table_2006,
          PARTITION big_table_2007)
    UPDATE GLOBAL INDEXES;

Partitioning vs Sharding

stackOverflow.

Horizontal partitioning splits one or more tables by row, usually within a single instance of a schema and a database server. It may offer an advantage by reducing index size (and thus search effort) provided that there is some obvious, robust, implicit way to identify in which table a particular row will be found.
Sharding goes beyond this: it partitions the problematic table(s) in the same way, but it does this across potentially multiple instances of the schema.
Sharding is the process of storing data records across multiple machines and is MongoDB’s approach to meeting the demands of data growth. As the size of the data increases, a single machine may not be sufficient to store the data nor provide an acceptable read and write throughput. Sharding solves the problem with horizontal scaling. With sharding, you add more machines to support data growth and the demands of read and write operations.

Functions and operators

Arithmatic Operators

dev.mysql.com.



SELECT 3/5;
// 0.60
SELECT 102/(1-1);
// NULL

// Integer division
SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;
// 2, -2, -2, 2

// Modulo
N % M
// Or
N MOD M

Cast Functions/Operators

BINARY operator

Cast a string to a binary string. BINARY str is shorthand for CAST(str AS BINARY).


SELECT 'a' = 'A';
// 1
SELECT BINARY 'a' = 'A';
// 0
SELECT 'a' = 'a ';
// 1
SELECT BINARY 'a' = 'a ';
// 0

CAST, CONVERT functions

CAST(expr AS type) is same as CONVERT(expr,type). The previous is standard SQL, and the latter is ODBC syntax.

CONVERT(expr USING transcoding_name) converts data between different character sets.
CAST(character_string AS character_data_type CHARACTER SET charset_name) is the same.


SELECT CONVERT('abc' USING utf8);
SELECT CONVERT(_latin1'Müller' USING utf8);
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

// LOWER() and UPPER() are ineffective when applied to binary strings.
SET @str = BINARY 'New York';
SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
// +-------------+-----------------------------------+
// | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
// +-------------+-----------------------------------+
// | New York    | new york                          |
// +-------------+-----------------------------------+

// Create one table from another.
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

// Change the sorting of enum from internal numeric order to lexical order.
SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

// Automatic cast:
SELECT 1+'1';
// 2
SELECT CONCAT('hello you ',2);
// 'hello you 2'

SELECT CAST(1-2 AS UNSIGNED);
// 18446744073709551615
SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
// -1

Comparison operators

>=	Greater than or equal operator
<=	Less than or equal operator
wcfNote: =< or => is unacceptable.

create

dev.mysql.com: partitioning exchange.


CREATE TABLE es3 LIKE e;
ALTER TABLE es3 REMOVE PARTITIONING;
ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;

CREATE TABLE t1 (
    year_col  INT,
    some_data INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2002),
    PARTITION p4 VALUES LESS THAN (2006),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);
-- VALUES LESS THAN clauses work sequentially in a manner similar to that of the case portions of a switch ... case block.

CREATE TABLE rc (
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

alter

wcfNote: there is also other alterable things, such as "alter database", "alter server", "alter function" etc. Except for "create table", "create" can also "create index" etc. See dev.mysql.com for instance, its left-side panel shows such DDL's.

table



-- Modify column definitions
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;

-- Add UNIQUE
ALTER TABLE TABLE_NAME ADD Id INT IDENTITY(1,1) PRIMARY KEY
ALTER TABLE TABLE_NAME ADD CONSTRAINT constr_ID UNIQUE (user_id, game_id, date, time)

-- Drop constraints.
-- MySQL consider a unique constraint to be an index.
-- https://codeghar.wordpress.com/2008/03/28/drop-unique-constraint-in-mysql/
ALTER TABLE t DROP INDEX unique_constraint_1;

-- Drop primary keys.
-- https://stackoverflow.com/questions/2111291/remove-primary-key-in-mysql
-- Error: there can be only one auto column and it must be defined as a key. Explanation: Without an index, maintaining an autoincrement column becomes too expensive, that's why MySQL requires an autoincrement column to be a leftmost part of an index. The autoincrement property should be removed before dropping the key:
-- Suppose the primary key previously is: (`id`, `another_id`) where `id` is auto-increment.
ALTER TABLE user_customer_permission MODIFY id INT NOT NULL;
ALTER TABLE user_customer_permission DROP PRIMARY KEY;
-- Better: One liner, to remove `another_id` from the primary key
ALTER TABLE `user_customer_permission` DROP PRIMARY KEY, ADD PRIMARY KEY ( `id` )

rename

dba.stackExchange.com: how do I swap tables in MySQL.


# It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.
RENAME TABLE foo TO foo_old, foo_new To foo;

show



SHOW CREATE TABLE es3\G
//        Table: es3
// Create Table: CREATE TABLE `es3` (
//   `id` int(11) NOT NULL,
//   `fname` varchar(30) DEFAULT NULL,
//   `lname` varchar(30) DEFAULT NULL
// ) ENGINE=InnoDB DEFAULT CHARSET=latin1
// 1 row in set (0.00 sec)

show processlist

dev.mysql.com.


SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

Very useful if you get the “too many connections” error message and want to find out what is going on.

Each connection to mysqld runs in a separate thread. You can kill a thread with the KILL processlist_id statement.

Show warnings

If mysql says there are warnings, you could use show warnings; to see what they are. http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html.

Show Variables


show variables like 'hostname';

show global variables like 'table%'; 
+----------------------------+-------+ 
| Variable_name | Value | 
+----------------------------+-------+ 
| table_definition_cache | 1900 | 
| table_open_cache | 3000 | 
set global table_open_cache = 6000;

# http://erlycoder.com/39/mysql-partitioning-subpartitioning-for-mysql-scalability
SHOW VARIABLES LIKE '%have%';

Show plugins, ...


SHOW PLUGINS; /* E.g. see if partition is installed. */

Add


ALTER TABLE tblName ADD COLUMN (`uuid` VARBINARY(255)), LOCK=NONE, ALGORITHM=INPLACE

Drop

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.

Insert


insert into table1 values (5, DEFAULT, 10, DEFAULT)

replace

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Select


SELECT column_name(s) FROM table_name;
/* Only list the top number items. */
SELECT column_name(s) FROM table_name LIMIT number;

/* Save top 1 result to file. */
select inst_attrs into dumpfile 'meta.raw' from instance limit 1;

/* select 1 from table will return the constant 1 for every row of the table. It's useful when you want to cheaply determine if record matches your where clause and/or join. */
/* https://stackoverflow.com/questions/7171041/what-does-it-mean-by-select-1-from-table */
SELECT 1 FROM TABLE2 T2 WHERE ...

/* Calculate the length of the column in bytes. */
select length(inst_attrs) from instance limit 1;

/* List all columns' title. */
show columns from tableName;

CREATE INDEX id_index ON tableName (id);
CREATE UNIQUE INDEX id_index ON tableName (id);

ALTER TABLE instance
	ADD INDEX series_fk (series_fk),
	ADD CONSTRAINT series_fk FOREIGN KEY (series_fk) REFERENCES series(pk);

/* Find all non-empty tables from table pacsdb. */
select table_name, table_rows from information_schema.tables where table_rows>=1 and table_schema = 'pacsdb';

/* ---- \G ------ */
/* Use \G instead of ; to make output vertical */
SELECT * FROM help_keyword LIMIT 3\G

/* Select non-empty column. */
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

Select Non-empty columns.

Update

UPDATE [LOW_PRIORITY] [IGNORE] table_references
	SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
	[WHERE where_condition]

Multi tables

mysqlTutorial: mysql update join.



UPDATE employees
  INNER JOIN
    merits ON employees.performance = merits.performance 
SET 
  salary = salary + salary * percentage; /* percentage is a column from merits.*/

/* -- Update column in a table whose values are not found in another table: --*/

/* For employees who has no performance, set the percentage to 0.015.
Using LEFT JOIN instead.
Notice the WHERE clause filters out those having performance.*/

UPDATE employees
  LEFT JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    merits.percentage IS NULL;

Time

Now()


/* String: 'YYYY-MM-DD HH:MM:SS' or Number: YYYYMMDDHHMMSS.uuuuuu.*/
SELECT NOW();
/* -> '2007-12-15 23:50:26', return as string. */
SELECT NOW() + 0;
/* -> 20071215235026.000000, return as a number. */

NOW() returns a constant time that indicates the time at which the statement began to execute, which differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

Set variables

MySQL reference.


/* User variable is written as @userVar. */
set @userVar = expr;

/* Global and Session/local variable. */
SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
/* @@ indicates a session var. */
SET @@sort_buffer_size=1000000;

/* The following two statements are identical: */
SET max_join_size=DEFAULT;
SET @@[email protected]@global.max_join_size;

Select can be used to show the variable values. MySQL returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value , which always refers to the session value.)


/* To display system variables names and values */
SHOW VARIABLES;

set @c=1, @[email protected]+1;
select @c, @d; # @d will be null

Aggregate functions

group by


select COUNT(*),table_name from proj_logs group by `table_name`;

group_concat, group by


/* Iteye reference. */
/* Concatenate names with same id to a row. */
select id,group_concat(name) from tabName group by id;

/* Use semicolon as separator. */
select id,group_concat(name separator ';') from tabName group by id;

/* Erase the duplicate names. */
select id,group_concat(distinct name) from tabName group by id;

/* Order names. */
select id,group_concat(name order by name desc) from aa group by id;

/* MySQL reference. */
/* The result is truncated to group_concat_max_len system variable, which has a default value of 1024.*/
/* To change it, */
SET [GLOBAL | SESSION] group_concat_max_len = val;

A more detailed example:


CREATE TABLE `mytbl` (
  `id` int,
  `Name` varchar(10),
  `Value` int
);

INSERT INTO `mytbl` (`id`, `name`,`value`) VALUES
(1,'A', '4'),
(1,'A', '5'),
(1,'B', '8'),
(2,'C', '9');

The following example shows how group_concat works.


select group_concat(`Value` separator ',') as `Name` from mytbl
	group by id;
/* Result:
NAME
4,5,8
9
*/

select group_concat(`Value` separator ',') as `Name` from mytbl
	group by id, `name`;
/* Result:
NAME
4,5
8
9
*/

Count

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.


SELECT COUNT(DISTINCT results) FROM student;

SELECT student.student_name,COUNT(*)
	FROM student,course
	WHERE student.student_id=course.student_id
	GROUP BY student_name;

Prepare, Execute

You can use this fiddle to see the result. See also the overflow discussion where the question was raised.

Suppose we create a database like this:


CREATE TABLE trn_user_log
	(`app_id` int, `transaction_id` int, `mobile_no` varchar(15), `node_id` int, `customer_attribute` datetime, `entered_value` int);

INSERT INTO trn_user_log
	(`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`)
VALUES
	(100, 111, '9999999999', 1, '2001-01-01 00:00:00', 2),
	(100, 111, '9999999999', 2, '2001-02-01 00:00:00', 1),
	(100, 111, '9999999999', 3, '2001-03-01 00:00:00', 4),
	(100, 111, '9999999999', 4, '2001-04-01 00:00:00', 3),
	(100, 111, '9999999999', 5, '2001-05-01 00:00:00', 2),
	(100, 222, '8888888888', 4, '2001-04-01 00:00:00', 1),
	(100, 222, '8888888888', 3, '2001-03-01 00:00:00', 2),
	(100, 222, '8888888888', 2, '2001-02-01 00:00:00', 1),
	(100, 222, '8888888888', 1, '2001-01-01 00:00:00', 3),
	(100, 222, '8888888888', 5, '2001-05-01 00:00:00', 4);

When we execute codes as follow:


SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE node_id when ',
      node_id,
      ' then entered_value else NULL END)) AS user_input',
      node_id
    )
  ) INTO @sql
FROM trn_user_log;

select @sql;

/* @sql has content: */
/*
GROUP_CONCAT((CASE node_id when 1 then entered_value else NULL END)) AS user_input1,GROUP_CONCAT((CASE node_id when 2 then entered_value else NULL END)) AS user_input2,GROUP_CONCAT((CASE node_id when 3 then entered_value else NULL END)) AS user_input3,GROUP_CONCAT((CASE node_id when 4 then entered_value else NULL END)) AS user_input4,GROUP_CONCAT((CASE node_id when 5 then entered_value else NULL END)) AS user_input5
*/

SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql,
	'FROM trn_user_log 
	GROUP BY app_id, transaction_id, mobile_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Delete

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Delete all rows from these three tables. The order is important since series contains foreign keys to study, and study contains foreign keys to patient.


delete from series;
delete from study;
delete from patient;

desc

dev.mysql.com: explain.

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan.

in


/* Find city of "Paris" or "London": */
SELECT * FROM Customers
  WHERE City IN ('Paris','London');

like


/* Pattern starts with 's'. */
SELECT * FROM Customers
WHERE City LIKE 's%';

/* Pattern ends with 's'. */
SELECT * FROM Customers
WHERE City LIKE '%s';

/* Pattern contains 's'. */
SELECT * FROM Customers
WHERE Country LIKE '%s%';

/* Pattern does not contain 's'. */
SELECT * FROM Customers
WHERE Country NOT LIKE '%s%';

/* City starting with "a", "b", or "c" */
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

w3schools: sql wildcards.

Joins


/* inner join */
SELECT column_name(s)
  FROM table1
  [INNER] JOIN table2
  ON table1.column_name=table2.column_name;

/* left join */
SELECT column_name(s)
  FROM table1
  LEFT [outer] JOIN table2
  ON table1.column_name=table2.column_name;

/* full outer join */
SELECT column_name(s)
  FROM table1
  FULL OUTER JOIN table2
  ON table1.column_name=table2.column_name;

Joins in Oracle

Cross join

Oracle. Produces the Cartesian product of two tables.


# The following SELECT statements are equivalent:
SELECT * FROM CITIES CROSS JOIN FLIGHTS
SELECT * FROM CITIES, FLIGHTS

# The following SELECT statements are equivalent:
SELECT * FROM CITIES CROSS JOIN FLIGHTS
  WHERE CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
SELECT * FROM CITIES INNER JOIN FLIGHTS
  ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT

# A CROSS JOIN operation can be replaced with an INNER JOIN where the join clause always evaluates to true
SELECT * FROM CITIES LEFT OUTER JOIN
  FLIGHTS INNER JOIN COUNTRIES ON 1=1
    ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
      WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'

# It can also be replaced with a sub-query.
SELECT * FROM CITIES LEFT OUTER JOIN
  (SELECT * FROM FLIGHTS, COUNTRIES) S
    ON CITIES.AIRPORT = S.ORIG_AIRPORT
      WHERE S.COUNTRY_ISO_CODE = 'US'

Natural join

It creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.


# If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
  USING (COUNTRY, COUNTRY_ISO_CODE)

union

Combine the result-set of two or more SELECT statements.


/* Duplicate results are removed */
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

/* Duplicate results are kept */
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

SQL statement syntax

Data manipulation statements

Load data infile

mysqlDev.


LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
  [REPLACE | IGNORE]
  INTO TABLE tbl_name
  [PARTITION (partition_name,...)]
  [CHARACTER SET charset_name]
  [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
  ]
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
  ]
  [IGNORE number {LINES | ROWS}]
  [(col_name_or_user_var,...)]
  [SET col_name = expr,...]

mysqlimport --local --compress datafile

Special datatypes: ENUM, BIT

ENUM

LOAD DATA INFILE regards all input as strings, so you cannot use numeric values for ENUM or SET columns the way you can with INSERT statements. All ENUM and SET values must be specified as strings.

BIT

BIT values cannot be loaded using binary notation (for example, b'011010').


# cat /tmp/bit_test.txt
# 2
# 127
# mysql test
LOAD DATA INFILE '/tmp/bit_test.txt'
  INTO TABLE bit_test (@var1)
  SET b = CAST(@var1 AS UNSIGNED);

SELECT BIN(b+0) FROM bit_test;
# +----------+
# | bin(b+0) |
# +----------+
# | 10       |
# | 1111111  |
# +----------+

Fields, Lines

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:


FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

If you have generated the text file on a Windows system, you might have to use "LINES TERMINATED BY '\r\n'" to read the file properly.

ENCLOSED BY, ESCAPED BY in fields

if ENCLOSED BY '"' is specified

"The ""BIG"" boss"  - The "BIG" boss
The "BIG" boss      - The "BIG" boss
The ""BIG"" boss    - The ""BIG"" boss
OPTIONALLY in fields

# without OPTIONALLY.
"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

# With OPTIONALLY.
1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20
LINES TERMINATED BY

LINES TERMINATED BY is still used to separate lines. If a line does not contain all fields, the rest of the columns are set to their default values.

If you do not have a line terminator, you should set this to ''. In this case, the text file must contain all fields for each row.

An example

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

If the data file looks like this:


xxx"abc",1
something xxx"def",2
"ghi",3

The resulting rows will be ("abc",1) and ("def",2).

Import CSV

To import CSV, where lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names:


LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

Handling of NULL

NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is "\").

If FIELDS ENCLOSED BY is not empty, e.g. '"':

NULL - Interprete as NULL.
"NULL" - Interprete as string 'NULL'.

If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

column list, SET


LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

# Assigns the second input column to a user variable that is subjected to a division before being used for the value of t1.column2:
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

# Discard an input value by assigning it to @dummy
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

LOCAL

If LOCAL is specified
If LOCAL is not specified

# If db1 is the default database, it reads the file data.txt from the database directory for db1, not db2:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Pattern matching

Reference. "_" matches any single character and "%" matches an arbitrary number of characters. The pattern in case-insensitive by default.

Difference between = and like

http://stackoverflow.com/questions/543580/equals-vs-like. The = tests for equality while like is for pattern matching, thus the latter is slower.

SQL also supports regexp, using REGEXP or NOT REGEXP.


select count(*) from tablename where fieldname like "2015-06-06%";

Privileges

MySQL 5.7 privileges.

Process

The PROCESS privilege pertains to display of information about the threads executing within the server. The privilege enables use of SHOW PROCESSLIST, and SHOW ENGINE.

Other

Performance Schema

mysql.com.

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. It's used by Solarwinds.

Prepare mysql for mediawiki

Use mysql -u root -p to log in to mysql.


create database wiki;
create user 'wiki'@'localhost'; # Create user without password. MySQL stores passwords in user table in the mysql table.
SET PASSWORD FOR 'wiki'@'localhost' = PASSWORD('mypass'); # Set password, the mypass is encrypted by PASSWORD.
create database wiki;
GRANT ALL PRIVILEGES ON wiki.* to wiki; # Root grant privileges to user wiki.
flush privileges; # Update.

See reference.

Commands

Use mysql -u username [-h hostname] -p databaseName to log in.


select user from mysql.user; # Show all users.
show databases;	# List all databases.
use databaseName; # Switch to the database.
show tables; # Show all tables.
desc tableName; # Describe a table.
grant all privileges on databaseName.tableName to 'userName'; # Give privileges to the user.
revoke all privileges on databaseName.tableName from 'userName'; # Revoke privileges.
drop user 'userName'; # Delete the user.
flush privileges; # Always remember to update the privileges.

InnoDB

InnoDB is a high-performance storage engine for MySQL. To see whether your server supports InnoDB, type show engines; .

ACID: An acronym standing for atomicity, consistency, isolation, and durability. A database conforming to ACID is said to be a transactional database, which is robust to system failure and concurrent visit. There it is guaranteed that a transaction produces "All or None" effect on the database.

Cpp programming

C API

This page explains how to use the C API bundled with MySQL itself. The mysql_config --libs and mysql_config --cflags are very good utils.

ExecuteQuery()


// Query if an item exists.
string sqlStr = "select * from series where seriesIUID='";
sqlStr += seriesIUID + "';";
if (0 >= stmt->executeQuery(sqlStr.c_str())->rowsCount()) {
    // No such series.
    return -1;
}

Execute()

http://dba.stackexchange.com/questions/24542/execute-vs-executequery-in-mysql-connector-c.

A complete tutorial: here.

executeUpdate()

The method executeUpdate executes the given SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE, or an SQL statement that returns nothing, such as a DDL statement. executeUpdate() returns the row count for INSERT, UPDATE or DELETE statements and 0 for SQL statements that return nothing.

Connector/ODBC

Connector/ODBC is a standardized database driver for Windows, Linux, Mac OS X, and Unix platforms.

Connector/C++

I found no suitable RPM from Cent OS repositories, and then went to the MySQL website to download the C++ connector RPM.

Errors

Reference.

Security

SQL injection

w3schools.com: sql injection.

SQL Injection Based on 1=1 is Always True


txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

// When txtUserId = "105 or 1=1":
// It will return all rows from the table Users, since WHERE 1=1 is always true.
// SELECT * FROM Users WHERE UserId = 105 or 1=1

SQL Injection Based on ""="" is Always True


uName = getRequestString("UserName");
uPass = getRequestString("UserPass");

sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"

// uName: " or ""="
// uPass: " or ""="
// Now where clause is always TRUE:
// SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

SQL Injection Based on Batched SQL Statements


txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

// txtUserId: 105; DROP TABLE Suppliers
// The SQL becomes:
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers

Solution

The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.
SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.


txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

Blacklisting words or characters (delete, drop, semicolons and quotation marks, .etc) is not a very good idea. Many of these are used in common language, and should be allowed in many types of input.

Optimization

Optimizing SQL statements

Optimizing SELECT

Optimizing INSERT

The time required for inserting a row is determined by the following factors:

Advices:

Optimizing UPDATE

MySQLDev.

FAQ

How to start mysqld on Mac OS?

Go to perferences, and click on mysql panel. There is a "Start Mysql Server" button.

Why left join returns more rows than left table?

StackOverflow: how can a left outer join return more records than exist in the left table.

The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.
If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN.


# Table1                Table2
# _______               _________
# 1                      2
# 2                      2
# 3                      5
# 4                      6

SELECT Table1.Id, Table2.Id FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Id=Table2.Id

# Results:
# 1,null
# 2,2
# 2,2
# 3,null
# 4,null

TBD


select * INTO OUTFILE '/tmp/result.txt'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '""'  LINES TERMINATED BY '\n' from study limit 5;

Allow root to login remotely

StackOverflow.


# Grant privileges. As root user execute:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

# bind to all addresses:
# The easiest way is to comment out the line in your my.cnf file:
bind-address = 127.0.0.1 
# and restart mysql
service mysql restart

# To check where mysql service has binded execute as root:
netstat -tupan | grep mysql

Unknown variable: default-character-set=utf8

StackOverflow. Edit /etc/my.conf, and comment out "default-character-set" line. Use instead: character-set-server = utf8

Reset root passwd

Method 1

Mysql dev page.


sudo mysqladmin -u root shutdown

mysqld_safe --skip-grant-tables &
# Now you could login mysql with root account with no password.

# Log on to mysql, and type mysql commands. See below.

sudo mysqladmin -u root shutdown (it is very important to stop the server at this stage).

sudo service mysqld restart

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Method 2

Ref. Execute "mysqld_safe --init-file=/home/me/mysql-init &". The file mysql-init contains:


UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Create user if not exists

Ref. MySQL does not support create user if not exists syntax as it does with create table and create procedure. This is pretty weird, as it just boils down to checking whether single row exists in the `mysql`.`user` table.

Fortunately, there is a simple work-around for this. The MySQL's grant statement creates the user you're granting some rights to - which you typically do right after you've created the user - if the user does not exist (as long as the no_auto_create_user is not set).

Also, the grant statement allows you to provide the password for the user, making the create user statement pretty much useless.

To make sure the user exists, identified with password, and granting all rights on the database when connecting from localhost, use

grant all on `database`.* to 'user'@'localhost' identified by 'password';

show grants for user;

Error: Lock wait timeout exceeded

so: getting lock wait timeout exceeded try restarting transaction.

Try to find what is locking the table:



// See the list of locked tables
show open tables where in_use>0;

// See the list of the current processes, one of them is locking your table(s)
show processlist;
// If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
show full processlist;
// Kill one of these processes
kill <put_process_id_here>;