This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
morgo@rbook:~/sandboxes/msb_5_6_16$ cat /tmp/my.cnf | |
[mysql] | |
user=root | |
password=testtttt | |
------------------------------------------------------------------------------------------------------------------------------------- 15:16:39 | |
morgo@rbook:~/sandboxes/msb_5_6_16$ ~/Documents/mysql-installs/5.6.16/bin/mysql --defaults-extra-file=/tmp/my.cnf --defaults-file=/Users/morgo/sandboxes/msb_5_6_16/my.sandbox.cnf | |
Welcome to the MySQL monitor. Commands end with ; or \g. | |
Your MySQL connection id is 15 | |
Server version: 5.6.16-log MySQL Community Server (GPL) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Example showing zero date: | |
mysql [localhost] {msandbox} (test) > CREATE TABLE a (a INT NOT NULL primary key auto_increment, b date); | |
Query OK, 0 rows affected (0.05 sec) | |
mysql [localhost] {msandbox} (test) > INSERT INTO a VALUES (NULL, '2014-06-00'); | |
Query OK, 1 row affected (0.01 sec) | |
# MySQL 5.7 proposal is to merge the SQL mode NO_ZERO_IN_DATE into the definition of STRICT_ALL_TABLES | |
# Which is on by default. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql [localhost] {msandbox} (test) > set sql_mode='STRICT_TRANS_TABLES'; | |
Query OK, 0 rows affected (0.00 sec) | |
mysql [localhost] {msandbox} (test) > CREATE TABLE string_test (a int); | |
Query OK, 0 rows affected (0.12 sec) | |
mysql [localhost] {msandbox} (test) > insert into string_test VALUES ('hello'); | |
ERROR 1366 (HY000): Incorrect integer value: 'hello' for column 'a' at row 1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> CREATE TABLE `wp_options` ( | |
-> `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
-> `option_name` varchar(64) NOT NULL DEFAULT '', | |
-> `option_value` longtext NOT NULL, | |
-> `autoload` varchar(20) NOT NULL DEFAULT 'yes', | |
-> PRIMARY KEY (`option_id`), | |
-> UNIQUE KEY `option_name` (`option_name`) | |
-> ) ENGINE=InnoDB AUTO_INCREMENT=43361 DEFAULT CHARSET=utf8; | |
Query OK, 0 rows affected (0.02 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', i on |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'; | |
+---------------+---------------------------------------------------------------+ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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) |
OlderNewer