MySQL Tricks

Here’s a few tricks that required more than a few minutes of Googling to uncover:

How to do a multi-row insert driven off a subquery

insert into tbl_blah (user_id, thing_id)
(
  select other_user_id, other_thing_id
  from tbl_other
  where some_criteria = other_criteria
)

Dealing with Dodgey Characters Stored In Text Fields

Finding rows with dodgey data (in this case, rogue line feeds):

select text
from tbl_blah
where text like concat( "%", char(10), "%")

To replace characters when retrieving data:

select replace( replace (text, "\r\n", " " ), ",", "" ) text
from tbl_blah

To permanently fix up the data in the table:

update tbl_blah
set text = replace( replace ( text, "\r\n", " " ), ",", "" )

The above example eliminates carriage returns that were added with Windows, as well as commas. Note: Mac/Unix carriage returns will be different. See this article for more info: http://www.xaprb.com/blog/2006/04/14/bad-character-data-in-mysql

The char() function can also be used in the replace() function, but unfortunately values over 127 will not work if the collation doesn’t support it. :-(

Displaying HEX

Simple:

select hex(response_text )
from tbl_blah
where blah_id = 9999

Note: the corresponding PHP function is bin2hex().

Starting and Stopping MySQL on OS X

To stop the server:

mysqladmin -uroot shutdown

or, on OS X:

sudo /Library/StartupItems/MySQLCOM stop

MySQLCOM is a script, so lots of juicy stuff in there! Although, having said that, it actually relies heavily on another script to do most of the heavy lifting: /opt/local/share/mysql/mysql.server

To start the server, assuming you are using the installer from the MySQL web site:

sudo /Library/StartupItems/MySQLCOM start

Setting Timezone in MySQL on OS X

And nary a my.cnf file was to be found, so to the last resort he turned - modifying the following file:

/opt/local/share/mysql/mysql.server

and modifying line ~200 as follows:

$bindir/mysqld_safe --datadir=$datadir --timezone=UTC --pid-file=$amp;pid_file >/dev/null 2>&1 &

Running a diff on two databases

Taken from this Stack Overflow question: http://stackoverflow.com/questions/225772/compare-two-mysql-databases.

If you’re working with small databases I’ve found running mysqldump on both databases with the –skip-comments and –skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.