Cool stuff in MySQL includes some conditional functions. These can be very useful and all that. Here's a quick run down of usage.
Diagnosing MySQL Startup Failure
If MySQL is set to start up automatically on boot up, but doesn't appear to be running, first try to restart the MySQL daemon from the command line. On Arch linux, do this:
sudo rc.d restart mysqld
If it's not already running, you'll see a FAIL when it tries to shutdown, and if there's a permanent issue preventing it starting, you'll see a FAIL when it tries to start up again. Check the error log for more info about what happened:
sudo tail -25 /var/
Obviously replace hostname
above with the name of the MySQL server.
InnoDB is notoriously fussy about its log files, and will prevent MySQL starting up if it cannot open the next expected logfile in the sequence. Check the error log for any suggestion that there was a problem opening the InnoDB log. This can happen if the file has become corrupted, or something prevented the file being created at the right time. Delete the /var/lib/mysql/mysql.index
file and restart MySQL, and the index and log sequence should be restarted.
Extract Domain from a referrer Field
SELECT COUNT( * ) AS
referrer, 9 ) ) AS
GROUP BY ref
ORDER BY num DESC
Copy/Rename an Entire MySQL Database
The best way to do this is to first create the target database. If you use the MySQL client, issue the command CREATE DATABASE new_database;
. Then run the following on the command line:
u root -
p old_database | mysql -
u root -
Once done, you can drop the old database if it's no longer needed.
Store IP Addresses in Native Format
In use, IP addresses are unsigned long integers, between 0 and 4294967295. They have a commonly recognised human-readable format of four dot-separated integers between 0 and 255 e.g. 184.108.40.206. For various reasons, including performance and compactness, when storing IP addresses in a database, it is preferable to store them as unsigned integers rather than character strings.
The PHP functions ip2long()
are able to convert between the human-readable and the native formats, but on 32 bit systems they must be used in conjunction with sprintf()
in order to get them as unsigned integers. For instance, say you have a human readable IP address in a variable called $ip
, you could convert it to the unsigned long format with the following:
This is a bit messy really, so you might be glad to know MySQL has had functions to perform the conversion since version 3.23.15. To convert a string format to the long format, use INET_ATON()
INSERT INTO ipAddresses SET ip=INET_ATON('220.127.116.11');
And to convert a long formatted IP to a human-readable one, use INET_NTOA()
SELECT INET_NTOA(ip) AS humanIp FROM ipAddresses;
Increase Maximum Size of a MySQL Database Table
By default, MyISAM tables are created with a four-byte datafile pointer. This creates an upper limit for the table of 4GB.
If you need to work with very large tables, you will want to change this. A five-byte datafile pointer will allow tables up to 1 terabyte (TB) in size. There is no apparent way to change the size of the datafile pointer directly, instead you need to tell MySQL the maximum number of rows you want the table to hold, and the average size of each row:
ALTER TABLE myMySQLTable max_rows=20000000 avg_row_length=250;
I don't believe these figures are used in any other context, but if you wish to make them better reflect your actual data, you can change the avg_row_length
to what you calculate to be the average size of a row, and then alter the max_rows
value to match. So if you halve avg_row_length
, you would double max_rows
and so on.
Version count by most recent
MySQL variables let you do a range of things. If a variable has not been defined already, then its value will be NULL
. As NULL + number = NULL
, you do need to initialise any variables before use. Sometimes you can do this in a single statement, other times you need a separate statement just for the initialisation. As long as the initialisation statement is in the same transaction as following statements which use those variables, they will work as expected.
$query = "SELECT IF( t.critical = 'Y', @version := @version +1, @version ) AS version,
IF( t.critical = 'N', @subversion := @subversion +1, @subversion :=0) AS subversion,
t.termsId, t.critical, CONCAT( DATE_FORMAT( t.lastUpdateDate, '%d/%m/%Y ' ) , DATE_FORMAT( t.lastUpdateTime, '%H:%i' ) ) AS updated,
DATE_FORMAT( t.lastUpdateDate, '%D %b %Y' ) AS updatedLong, s.username
FROM terms t
INNER JOIN security_user s ON t.lastUpdatedUser=s.id
ORDER BY version DESC, subversion DESC";
mysql_query("START TRANSACTION"); // A transaction is one way to initialise variables
mysql_query("SET @version :=0, @subversion :=0");
$archive = mysql_query($query);
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL that either inserts or updates see Section 18.104.22.168, INSERT ... ON DUPLICATE KEY UPDATE Syntax.
Get total rows of an item, the number of rows of that item meeting a particular criterion, and the percentage of the total that represents
`Auto Renew %
FROM packages d
Copy a table to a similar but not identical table
Say for instance you have one table like this:
As you might guess, the unixtime
column holds the time in Unix format (seconds since the first screening of Star Wars or something like that). The arbitaryFlags field has integer flags representing different situations, let's say 1 means 'Denied', 4 means 'Expired', and 8 means 'Success'. For various reasons, you decide to change the table format to the following:
The obvious way might be to create a quick script file to read in the rows from the old table, and insert them one by one into the new table. However, we can use a single SQL statement instead:
INSERT INTO newTable (
arbitaryFlags WHEN 1
THEN 1 END AS
Note the use of integer values in the CASE
statement. This works becuase ENUM column values can be specified using their numerical index as given in the column definition - but be aware: the index values start at 1, not 0. Alternatively, it would be perfectly fine to use the string values in place of the numbers, which would work just as well and avoid the possibility of using the wrong numbers.
Insert a row only if a similar row does not already exist
You can't use WHERE
clauses in INSERT
statements, but you can combine an INSERT
with a SELECT
that has a WHERE
clause. Combined with the special purpose dummy table DUAL
, we can construct an INSERT IF NOT EXISTS
type statement, that is not restricted to checking for duplicate keys and indexes:
INSERT INTO pollResponses (pollId, responseId, memberId, respondedOn)
SELECT ".intval($pollId).", ".intval($responseId).", ".$_SESSION['memberId'].", NOW()
FROM DUAL WHERE NOT EXISTS (SELECT pollResponseId FROM pollResponses WHERE pollId=".intval($pollId)." AND memberId=".$_SESSION['memberId']
Using the GROUP_CONCAT() MySQL Function
When grouping rows, different values are 'squashed' into one arbitary selected value from all in each group. For instance if you group a list of products on for example a category, you would get just one arbitrarily selected description returned. The GROUP_CONCAT()
function is an extension to MySQL which allows you to get all the different values of any such 'squashed' column.
By default, it will get you all different values, each separated by a comma. You can use DISTINCT to remove duplicates from its result, and you can use ORDER BY to order the list ascending or descending. Additionally, there is a special keyword, SEPARATOR, which lets you change or remove the comma between values.
When grouping returned rows with a GROUP BY
clause, this MySQL extension will give you a string containing all the values for certain columns concatenated into one. Any columns in expr
can be preceded by DISTINCT
in order to eliminate duplicate values. Each value is by default separated by a comma, but you can specify a separator of your choosing with the SEPARATOR
keyword, eg SEPARATOR ' '
to separate each value by a space. You can also specify the order in which the different values should be listed with an ORDER BY
clause. Here's an example:
studentName ORDER BY studentName SEPARATOR ':')
GROUP BY courseCode
The maximum length of the concatenated values is by default limited to 1024 characters, although it can be changed by adjusting the group_concat_max_len
Create a CSV file from the results of a query
Simply append the following to the end of your query:
INTO OUTFILE '/tmp/report.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Creating the file in tmp
is often simplest as that location is usually set to allow files to be created by just about any user.
Creating Random Password Strings
If you want to create a user with a random password, you can do it in MySQL:
INSERT INTO users SET password=
The above would create a random 5 letter password (consisting of random capital letters e.g. IHJDZ).
Deleting a Row and Shifting auto_index column
delId := 1742205;
DELETE FROM invoices WHERE invoiceId=@
DELETE FROM invoiceItems WHERE invoiceId=@
UPDATE invoices SET invoiceId=@
delId WHERE invoiceId=@
UPDATE invoiceItems SET invoiceId=@
delId WHERE invoiceId=@
ALTER TABLE invoices AUTO_INCREMENT =@
Creating Test Data on Consecutive Weekdays
days_ago = 80;
INSERT INTO timeLog SELECT NULL, 191,
days_ago DAY), '%w')=5,
days_ago := @
days_ago := @
DAY), 7, 30,
description, 0, 0, 'y'
Grant for all Databases with Wildcards
Use backticks in combination with SQL wildcards when using GRANT:
GRANT ALL ON `%_tests`.* TO 'phpunit'@'localhost';