Posted by: Paul on: September 16, 2009
1. Produce terminal-friendly output Occasionally you may be using the command line and find yourself limited by the size of either the table you’re getting results from or by the size of the terminal window you’re using. (I find the DPS window particularly resistant to resizing. You can get results in this format column1_name: data [...]
Posted by: Paul on: March 18, 2008
Join tutorials always tell you how to join tables in the same database, but what if you need to join tables which sit in different databases? Turns out this is nice and simple[1] (in MySQL, your db engine may vary): SELECT this-db.table-one.column-one, other-db.table-two.column-two FROM this-db.table-one LEFT JOIN other-db.table-two ON this-db.table-one.id = other-db.table-two.id Simply put, the [...]
Posted by: Paul on: March 17, 2008
We’re planning on upgrading from an archaic mysql engine (3.58) to the latest, shiny release version (5.1), so I’ve undertaking a code review of some of our administration applications to see what this unearthed. Turns out the password hashing for the MySQL native PASSWORD() function changed in version 4.1, so our login system won’t work [...]
Posted by: Paul on: February 28, 2008
MySQL’s REGEXP function is handy when you need to search for specific text strings within a textual column, but over large record sets, it’s performance can be less than fantastic. Recently I had a situation at work where I needed to do a first name / last name search across a single name field. Due [...]
Posted by: Paul on: October 29, 2007
Another MySQL timesaver: INSERT INTO Sales(TransId, Status, Amount) VALUES(123, ‘Pending’, 20) ON DUPLICATE KEY UPDATE Status = ‘Paid’ Via Particletree ———— Need to improve your MySQL skills? I recommend: High Performance MySQL: Optimization, Backups, Replication, and More
Posted by: Paul on: October 29, 2007
I really wish I’d known this a long, long time ago: insert into `mytable` (fname, lname, address) values (“john”, “doe”, “123 some st”), (“jane”, “doe”, “456 other st”) This wee gem allows an application to add multiple rows to a table in one simple query. Say goodbye to those processor intensive loops…
Posted by: Paul on: April 26, 2007
“You can’t create a table when it already exists. Moron.”
Posted by: Paul on: March 6, 2007
MySQL server says: “There’s something wrong with your query. I cannot magically turn your crap, buggy, invalid SQL into good code” Like the previous post, the error message won’t be shown in phpMyAdmin when using older versions of MySQL, just the error number.
recent comments