MySQL

Updated: 11 April 2024

Insert into table the result of a select query

INSERT INTO orders (col1, col2, col3)
SELECT foo, bar, baz
FROM our_table
WHERE condition;

Updated: 11 April 2024

Subqueries with ANY

The ANY keyword means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.”

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query

SELECT * FROM t1
WHERE column1 = ANY (
    SELECT column1 FROM t2
    WHERE t2.column2 = t1.column2
);

Updated: 11 April 2024

Unexpected results can occur if mysql mode is not set. On a fresh cloud9 workspace (for example) mysql server has no mode settings

mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+

Consequently truncation and attempts to insert the wrong types can pass without error

mysql> CREATE TABLE TEST (boolfield TINYINT(1), varcharfield VARCHAR(3));
mysql> INSERT INTO TEST VALUES ('A', 'wxyz');                                                                    
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SELECT * FROM TEST;                                                                                        
+-----------+--------------+
| boolfield | varcharfield |
+-----------+--------------+
|         0 | wxy          |
+-----------+--------------+

Two warnings are issued but the query does run ok and results in an unintended boolfield value and a truncation.

Now set (one of many possible) sql modes

mysql> SET @@SQL_MODE = 'STRICT_TRANS_TABLES';

mysql> INSERT INTO TEST (boolfield) VALUES ('A');                                                          
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'boolfield' at row 1

and

mysql> INSERT INTO TEST (varcharfield) VALUES ('wxyz');                                                                                                                                 
ERROR 1406 (22001): Data too long for column 'varcharfield' at row 1

Updated: 31 March 2024

This is how to enable the log table and query it directly with SQL

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT * FROM mysql.general_log ORDER BY event_time DESC;

Enable logging temporarily and write the log entries to a file

SET global log_output = 'FILE';
SET global general_log_file='/var/log/mysql/mysql-all.log';
SET global general_log = 1;

Updated: 22 February 2024

Run script on database, redirect output to log

mysql -u root -p db_name < sql_statement_file.sql >> query.log 2>&1

Run script on database, display results in a table

mysql --table -u the_user -pthe_user_pass -P 3307 -h 127.0.0.1 db_name < the-query.sql

Check if remote mysqld requires secure connections

mysql --ssl-mode=DISABLED -h example.com -u root -p

Execute a statement directly

mysql -u root -p db_name -e "SELECT * FROM tbl_foo LIMIT 5"

Updated: 13 January 2023

Create a remote user

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password|caching_sha2_plugin BY 'password';  
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Change host for a user

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;

Show the authentication plugin for each user

select user, plugin from mysql.user;

Updated: 10 September 2022

User defined variables can be assigned a value in one statement and referred to in another statement.

User variables are written as @var_name

For SET, either = or := can be used as the assignment operator.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL.

One way to set a user-defined variable is by issuing a SET statement

SET @var_name = expr [, @var_name = expr] ...

Also see SELECT ... INTO statement.

Updated: 13 April 2024

Show columns statement

SHOW COLUMNS FROM City;

Query for big tables

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "my_table"
ORDER BY (data_length + index_length) DESC;

Disable foreign key checks

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

log_bin_trust_function_creators
SQLSTATE[HY000]: General error: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

select @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;
select @@log_bin_trust_function_creators;

Show the string which can be used to re-create the named stored function

SHOW CREATE FUNCTION f_gross_store_income;

Show the string which can be used to re-create the named stored procedure

SHOW CREATE PROCEDURE sp_rent_chart;