Useful SQL Commands

I recently needed to take a database with separate date and time columns, convert that combined date-time data into Unix time format (i.e. seconds since the epoch [1st Jan 1970]), and add a new column to the database with that data. It took me a fair bit of googling to get it all working, so here’s my notes.

Sorry this isn’t really in an article format – but I need this written down and available for the future… Also, I wrote a small Java app to read the separate date and time text files and spit out a timezone-correct unixtime text file, which I then added to a spreadsheet containing just the the primary key rows and exported it as tab-separated values suitable for MySQL (or really, MariaDB in this case) import.

Show the table structure for the ‘accidents’ table

DESCRIBE accidents;
	+-------------------+-------------+------+-----+---------+-------+
	| FIELD             | TYPE        | NULL | KEY | DEFAULT | Extra |
	+-------------------+-------------+------+-----+---------+-------+
	| OBJECTID          | VARCHAR(50) | YES  |     | NULL    |       |
	| ACCIDENT_NO       | VARCHAR(50) | NO   | PRI |         |       |
	| ABS_CODE          | VARCHAR(50) | YES  |     | NULL    |       |
	..................................................................
	| STAT_DIV_NAME     | VARCHAR(50) | YES  |     | NULL    |       |
	+-------------------+-------------+------+-----+---------+-------+
	63 ROWS IN SET (0.00 sec)

Count unique values in the UNIX_TIME column in the accidents table

SELECT COUNT(DISTINCT ACCIDENT_NO) FROM `accidents`;
	+-----------------------------+
	| COUNT(DISTINCT ACCIDENT_NO) |
	+-----------------------------+
	|                       68272 |
	+-----------------------------+

Find duplicate values in a column (cannot make column a primary key if there are dupes!)

SELECT ACCIDENT_NO FROM accidents GROUP BY ACCIDENT_NO HAVING COUNT(*) > 1;
	Empty SET (0.15 sec)

Make a column be the primary key for a table

ALTER TABLE accidents ADD PRIMARY KEY (ACCIDENT_NO);
	Query OK, 68272 ROWS affected (12.24 sec)              
	Records: 68272  Duplicates: 0  Warnings: 0

Drop the UNIX_TIME column

ALTER TABLE accidents DROP COLUMN UNIX_TIME;

Add a new column UNIX_TIME to the accidents table after the last column (STAT_DIV_NAME)
ALTER TABLE `accidents` ADD `UNIX_TIME` BIGINT UNSIGNED NULL DEFAULT 0 AFTER `STAT_DIV_NAME`;
	Query OK, 0 ROWS affected (9.67 sec)
	Records: 0  Duplicates: 0  Warnings: 0

Add an empty column with no default value

ALTER TABLE `temptable` ADD `UNIX_TIME` BIGINT UNSIGNED NULL;

Create a temporary table in memory with a single column called UNIX_TIME which is of type BIGINT

CREATE TEMPORARY TABLE temptable (UNIX_TIME BIGINT) ENGINE = MEMORY;
	Query OK, 0 ROWS affected (0.00 sec)

Import from local CSV file

I created a tab-separated CSV file which had just the ACCIDENT_NO as primary key and the UNIX_TIME field. We can then import that data into the temptable with:

LOAD DATA LOCAL INFILE '/home/r3dux/accident_no_and_unix_time.csv' INTO TABLE temptable FIELDS TERMINATED BY '\t' (ACCIDENT_NO, UNIX_TIME);
	Query OK, 68272 ROWS affected (0.09 sec)             
	Records: 68272  Deleted: 0  Skipped: 0  Warnings: 0

Load all the single-line data values fromthe UNIX_TIME.txt file into the UNIX_TIME column of our temp table

LOAD DATA LOCAL INFILE '/home/r3dux/UNIX_TIME.txt' INTO TABLE temptable FIELDS TERMINATED BY ',' (UNIX_TIME);
	Query OK, 68272 ROWS affected (0.03 sec)             
	Records: 68272  Deleted: 0  Skipped: 0  Warnings: 0

Merge the temptable with just the ACCIDENT_NO and UNIX_TIME into the accidents table using ACCIDENT_NO as the primary key

MariaDB [govhack2015]> UPDATE accidents JOIN temptable USING (ACCIDENT_NO) SET accidents.UNIX_TIME = temptable.UNIX_TIME;
	Query OK, 13375 ROWS affected (2.08 sec)
	ROWS matched: 68272  Changed: 13375  Warnings: 0

So in this particular instance where I had a partial update, I was missing 13,375 UNIX_TIME values!

Make sure this worked by setting all the UNIX_TIME values in the accidents table to zero, and then re-importing

MariaDB [govhack2015]> UPDATE accidents SET UNIX_TIME = 0;
	Query OK, 68272 ROWS affected (3.16 sec)
	ROWS matched: 68272  Changed: 68272  Warnings: 0
 
MariaDB [govhack2015]> UPDATE accidents JOIN temptable USING (ACCIDENT_NO) SET accidents.UNIX_TIME = temptable.UNIX_TIME;
	Query OK, 68272 ROWS affected (4.01 sec)
	ROWS matched: 68272  Changed: 68272  Warnings: 0

Now finally, we can drop the in-memory temptable!

DROP TABLE temptable;

Export the database

To export the database we can use the mysqldump utility (this is NOT within the MySQL command line!):

mysqldump -u root -p govhack2015 > govhack2015_database_dump.sql

Then to import this dump, let’s say we don’t have a `govhack2015` database

mysql -u root -p

Followed by (in the MySQL command line):

CREATE DATABASE govhack2015;
USE govhack2015;
SOURCE /home/r3dux/govhack2015_database_dump.sql;
SELECT COUNT(DISTINCT ACCIDENT_NO) FROM `accidents`;
	+-----------------------------+
	| COUNT(DISTINCT ACCIDENT_NO) |
	+-----------------------------+
	|                       68272 |
	+-----------------------------+

It all works! =D

How to stop a MySQL action which is taking forever… (like when I bodge a merge) – in a different MySQL commandline interface

SHOW PROCESSLIST;
KILL the-id-number-of-the-hung-or-otherwise-borked-operation;