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.

Yet Another Programming Blog

Where James Gordon rambles about PHP, Laravel and web development in general.

Find me on Google Plus Find me on Twitter Find me on Stack Exchange Find me on Github Subscribe