MySQL Coolness

Cool stuff in MySQL includes some conditional functions. These can be very useful and all that. Here's a quick run down of usage.

compton, 28 September 07

Updated 19 May 10

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);
mysql_query("COMMIT");

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 12.2.4.3, 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

SELECT COUNT(d.packageId) AS Total, SUM(IF(d.renewalMethod='auto',1,0)) AS `Auto Renew`, SUM(IF(d.renewalMethod='auto',1,0))/COUNT(d.packageId)*100 AS `Auto Renew %`
FROM packages d
WHERE d.purchaseDate>'2007-08-19' AND d.domainProductId>0;

Copy a table to a similar but not identical table

Say for instance you have one table like this:

id	foreignKey	unixtime	arbitaryFlags
int(11)	int(11)	int(14)	int(4)

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:

id	foreignKey	normalTime	activity
int(11)	int(11)	datetime	enum('Success','Denied','Expired')

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 (foreignKey, normalTime, activity)
SELECT foreignKey, FROM_UNIXTIME(unixtime) as normalTime,
CASE arbitaryFlags WHEN 1 THEN 2 WHEN 4 THEN 3 WHEN 8 THEN 1 END AS activity
FROM oldTable;

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:

SELECT COUNT(studentId), GROUP_CONCAT(studentName ORDER BY studentName SEPARATOR ':')
FROM students
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 system variable.

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=CONCAT(CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65));

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

BEGIN;
SET @delId := 1742205;
DELETE FROM invoices WHERE invoiceId=@delId;
DELETE FROM invoiceItems WHERE invoiceId=@delId;
SELECT @moveId:=max(invoiceId) FROM invoices;
UPDATE invoices SET invoiceId=@delId WHERE invoiceId=@moveId;
UPDATE invoiceItems SET invoiceId=@delId WHERE invoiceId=@moveId;
ALTER TABLE invoices AUTO_INCREMENT =@moveId;
COMMIT;

Creating Test Data on Consecutive Weekdays

SET @days_ago = 80;
INSERT INTO timeLog SELECT NULL, 191, NOW(), 191,
DATE_SUB(CURRENT_DATE, INTERVAL (IF(DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL @days_ago DAY), '%w')=5,
@days_ago := @days_ago-3, @days_ago := @days_ago-1)) DAY), 7, 30, newProjectId, 0, description, 0, 0, 'y'
FROM timeLog
WHERE userId=191 AND hours>=3 AND timeLogId<3575
LIMIT 60;