Praj's Blog

MySQL compare strings by case

Here’s one method of comparing strings by case in MySQL. You can use the md5 function to hash the string and compare to its lower case value.

This example is for checking for usernames that are not lower case in moodle.

select username, md5(username), md5(lower(username))
from mdl_user
where deleted = 0 and suspended = 0
and md5(username) != md5(lower(username));

If the md5 hash when the string is lower cased is different to the current md5 hash you will get a result.

MySQL group_concat()

The group_concat() function in MySQL is really nifty. It concatenates all rows that match your group by statement into a single, comma separated string. Essentially, it transposes rows to columns.

Take this example. Say you have a table that stores a set of locations by country, state and city.

You could then write SQL as follows:

select 
  country, 
  state, 
  group_concat(city) as cities 
from locations 
group by country, state

What this is saying is bring back 3 columns, country, state, and for each combination of country and state, a list of all of the cities in that country and state as the third column (called cities).

This is actually a phenomenally handy thing to be able to do, and something that is much harder to do in any other DBMS.

MySQL Engine

To see what storage engine is used by the server by default:

mysql> show variables like '%storage_engine';

To find out what storage engine your tables are using by schema:

mysql> select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables;

Update WordPress Site URL

When you move a WordPress installation (e.g. from production to your local development environment), you’ll need to update the wp_options table to reflect the new URL.

The following script (tested on MySQL) lets you find and replace all URLs that match the old url with a new one using a correlated update statement:

Just replace “http://www.oldurl.com” and “http://www.newurl.com” with your old and new URLs respectively.

update wp_options a inner join ( select option_id, replace(option_value, 'http://www.oldurl.com', 'http://www.newurl.com') as option_value from wp_options where option_value like 'http://www.oldurl.com%' ) b on a.option_id = b.option_id set a.option_value = b.option_value

Reversible Data Conversion SQL

If you work with IT systems, sooner or later you’ll have the need to write SQL to “fix” data in a database table (or tables). One key piece of advice is to make sure that the SQL script you come up with is reversible, just in case you need to change things back or make additional changes on the data set that was changed.

Here’s an example of what I mean:

If this was your data conversion script (to flag users as deleted):

update users
set deleted = 1 where id = (
select id from users
where to_be_deleted = 1
and deleted = 0);

Assuming that all deleted users have the flag “to_be_deleted” set to 1, this script is not reversible. Once run, how would you distinguish the data set you changed (set to deleted = 1) from all the other users that already have a flag of deleted = 1?

A few options here (there are many ways to solve this):

  • Set to_be_deleted to a value unique to your data change (e.g. to 99)
  • Generate individual update statements for each user id you are updating (use SQL to do this).
  • If there is a date/time stamp field use that to identify your change
  • If there is an “updated by user” type field use that to identify your change

The key is to think about the scenario, “after running this script, can I identify the data set that was changed easily?”