MySQL

Updated: 06 July 2024

Dynamically create a MySQL table which accommodates a CSV file, then import that CSV file.

#!/bin/bash

# create-table.sql
# CREATE TABLE `csv` (
#     `id` int(11) NOT NULL AUTO_INCREMENT,
#     PRIMARY KEY (`id`)
# );
mysql -u user -ppassword < create-table.sql

# Read column headers into an array.
IFS=',' read -ra COLS <<< $(head -n1 file.csv)

# Loop through the array and create a
# new table column for each csv column.
for COL in "${COLS[@]}"; do
    mysql -u user -ppassword -e "ALTER TABLE csv ADD \`$COL\` TEXT DEFAULT NULL;"
done

# load-csv-into-table.sql 
# LOAD DATA LOCAL INFILE 'file.csv'
# INTO TABLE `csv`
# FIELDS TERMINATED BY ',' ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# IGNORE 1 ROWS;
mysql -u user -ppassword < load-csv-into-table.sql

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: 14 October 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;

Check the value of log_output

SELECT @@global.log_output;

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: 04 October 2024

Install mysql client tools only (Ubuntu)

sudo apt-get install mysql-client

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 November 2024

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.