morgo

create table department (
    id bigint not null, 
    budget bigint not null, 
    name varchar(255), 
    primary key (id)
) ENGINE=InnoDB;

create table employee (

morgo

/* 
 * Procedure: show_profiles()
 *
 * Versions: 5.6.x
 *
 * Emulates previous behaviour of SHOW PROFILES feature which has been
 * officially deprecated from MySQL 5.7 onwards.
*/

morgo

Keybase proof

I hereby claim:

• I am morgo on github.
• I am morgo (https://keybase.io/morgo) on keybase.
• I have a public key whose fingerprint is 97A4 2DBE 3564 9BE6 0B37 E35C C00C 8FB5 BF35 ACE4

To claim this, I am signing this object:

morgo

Keybase proof

I hereby claim:

• I am morgo on github.
• I am morgo (https://keybase.io/morgo) on keybase.
• I have a public key whose fingerprint is BEAF CAD6 3F51 DF9A 1915 0356 6B38 6951 7695 2538

To claim this, I am signing this object:

morgo

# works in all versions
mysql> SHOW PROCESSLIST;
+----+----------+-----------+------+---------+------+----------+------------------+
| Id | User     | Host      | db   | Command | Time | State    | Info             |
+----+----------+-----------+------+---------+------+----------+------------------+
|  3 | msandbox | localhost | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+----------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

# using SYS (ships with MySQL 5.7)

morgo

mysql [localhost] {msandbox} (test) > SELECT version();
+---------------+
| version()     |
+---------------+
| 5.7.5-m15-log |
+---------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE 'sql_mode';
+---------------+---------------------------------------------------------------+

morgo

mysql [localhost] {msandbox} (test) > Create table test (id int primary key auto_increment, name char(20));
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;

morgo

mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','',                                                                                                                                                                                                          i                                                                                                                                                                               on                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

morgo

mysql [localhost] {msandbox} (test) > CREATE TABLE cities (id int not null primary key auto_increment, name VARCHAR(50) NOT NULL, INDEX(name));
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO cities (name) VALUES ('Toronto'), ('Montreal'), ('Vancouver'), ('Calgary');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > update cities set name = AES_ENCRYPT(name, 'MYKEY');
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

morgo

MySQL 5.5 and below default behaviour (can be changed with sql_mode):
————————————

mysql [localhost] {msandbox} (test) > select * from test_table;
+-----+------------+
| id  | some_value |
+-----+------------+
| 100 |          2 |
| 101 |       NULL |
| 102 |          2 |