Russell Beattie

I subscribed to the MySQL Cookbook on O'Reilly's Safari and it's filled with some *fantastic* tips. Definitely grab it if you're using MySQL.

Here's a couple of my favorites, which I never knew about. Using the command line client, you can end your queries with a \G and the results print out one row per line. like this:

mysql> select id, title, created from miniblog where parentId = 0 and id > 1006550 G
*************************** 1. row ***************************
     id: 1006552
  title: Jabber Goes Mobile
created: 2004-03-04 14:42:15
*************************** 2. row ***************************
     id: 1006560
  title: Back to SHOUTcast
created: 2004-03-05 10:30:58
*************************** 3. row ***************************
     id: 1006578
  title: Small World Story no. 42
created: 2004-03-06 11:30:04
*************************** 4. row ***************************
     id: 1006579
  title: Moving to ServerMatrix
created: 2004-03-06 12:00:30
4 rows in set (0.01 sec)

That's *soooo* helpful isn't it? Usually the results are presented in a badly wrapped table and pretty much impossible to read.

Next, if you're using MySQL 4.0, if you put a -X on the command line you can get the results of your query in XML!! This is *really cool*. The -X can be used with the query to output to the screen/file or it can be used interactively as well. What I want now is a way of getting to that XML from within Java! Here's an example (I'm not using MySQL on my personal server, so this is from the book):

mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="SELECT * FROM limbs WHERE legs=0">
  <row>
    <thing>squid</thing>
    <legs>0</legs>
    <arms>10</arms>
  </row>

  <row>
    <thing>octopus</thing>
    <legs>0</legs>
    <arms>8</arms>
  </row>

  <row>
    <thing>fish</thing>
    <legs>0</legs>
    <arms>0</arms>
  </row>
</resultset>

That is very, very cool. The final tip that blew my mind is the LAST_INSERT_ID() function and JDBC method. Basically I've just been doing this incorrectly for years, so it's nice to see the right way of getting the auto_increment id number of an inserted row. This function returns the id based on the *connection*, so there's no concern that you're going to get the wrong number if someone else also inserts a row at the same time. Also, the JDBC method doesn't need a second query to get the id.

SQL:

mysql> INSERT INTO insect (name,date,origin)
    -> VALUES('cricket','2001-09-11','basement');
mysql> SELECT LAST_INSERT_ID( );
+------------------+
| last_insert_id( ) |
+------------------+
|                9 |
+------------------+

JDBC: 

sql = "insert into miniblog(id, title, content, authorId, created) values(null, ?,?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, title);
pstmt.setString(2, content);
pstmt.setInt(3, authorId);
pstmt.setTimestamp(4, created);
pstmt.executeUpdate();

long entryId = ((com.mysql.jdbc.PreparedStatement) pstmt).getLastInsertID();

That is fantastic, no? Maybe I'm the last person to know this, but I'm happy to finally found a clue about inserts in MySQL.

MySQL just rocks, hands down. I think the Java guys need to give it more respect like the Perl/Python/PHP guys do. I've been screwing around with different ways of dealing with data in blogs for the past year - I've got at least 5 different versions of the software I use to produce this site, most of which separate the blog from the DB via XML and trying to genericize the app for distribution. But I think what would be *really* cool is to create a MySQL blog called "MyBlog" that ran on Tomcat and used as many MySQL specific tricks and shortcuts as possible (things like LIMIT and DISTINCT). I bet it would be fast, scalable, and insanely easy to write with JSTL.

I really do want a way to get to that XML output that MySQL produces though. Not that it's hard to do the same in using JDBC's ResultSetMetaData, but why go through that hassle of formatting all that if MySQL is doing it for you? I'd love to just create a query, pass it to MySQL and get a bytestream of character data back that I could just pass right off to a SAX or DOM object for processing and display (I love XPath for grabbing data out of an XML doc - it's better than any equivalent function in RDBs). I wonder if I could do this with just a socket connection? I hate to rewrite the JDBC spec, but the connections would need to be poolable, etc... hmm. It's definitely an interesting thought. Updates/Inserts would still need to be done via JDBC, but 90% of blog traffic is read-only, right?

Cool stuff.

-Russ