Some Useful MySQL Snippets

Every day at work, I run different MySQL queries to make my life as a developer a little easier. Tools such as phpMyAdmin can be a real benefit, but it’s often quicker to use the command line to run database commands.

Since I work with WordPress the most, here’s a handy snippet that no WordPress developer should be without; update your live database download so it works on your development environment.

Live Database Dump to Development Database

# Update wp_options table
UPDATE wp_options SET option_value = REPLACE(option_value,'jamie3d.com','jamie3d.dev');
# Update wp_posts table
UPDATE wp_posts SET guid = REPLACE(guid,'jamie3d.com','jamie3d.dev');

For the above snippet, it’s important to note that running this query on the options table can be risky if you have serialized data stored in the database. The easy fix is to make sure your domain name length doesn’t change. So I use jamie3d.dev because it has the same number of characters as jamie3d.com does. Essentially, changing the length of a serialized string corrupts it.

Update:
Here’s another way that uses variables: https://gist.github.com/jamiehs/9647075

https://gist.github.com/jamiehs/9647075

Create a Database & Assign a User

CREATE DATABASE database_name;
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost';

# To grant specific privileges, use this format instead:
GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* TO 'user_name'@'localhost';

A shorthand version of this command also exists, but I like the verbosity of the above command.

Relative Time Ago (in MySQL query)

# Relative Time
DATE_SUB( NOW( ) , INTERVAL 25 HOUR )

# For example:
# Select all users who were updated in the last 25 hours.
SELECT * FROM users
WHERE users.updated > DATE_SUB( NOW( ) , INTERVAL 25 HOUR );

# Select all users who were updated in the last 2 days.
SELECT * FROM users
WHERE users.updated > DATE_SUB( NOW( ) , INTERVAL 2 day );