Archive for the ‘databases’ Category
- In: databases | SQL
- 2 Comments
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
column2_name: data
instead of the default tabular layout by using the \G flag at the end of your select statement:
select * from my_table where id = "3"\G
2. Better, more flexible result ordering
Recently I found myself a bit hamstrung by the way a table had been designed. I wanted chronological ordering, but the default ordering by date wasn’t ordering the results exactly chronologically.
I found that I could use functions in the ‘order by’ component of my selects to get better ordering
select * from my_table order by concat(entry_date, entry_time);
(group by could also be used to similar effect)
3. Logging your command line session
Occasionally you may end a command line MySQL session and then think “Argh – I wish I’d saved that query!”. MySQL has a handy logging feature which writes your session (input and output) to a file so you can pull out those tricky queries and keep them for future reference. To do so, log in using the -tee flag:
mysql -u username -p -tee=/path/to/your/log.file
Obviously you need write access to the folder you’re writing to. If the file doesn’t exist, MySQL will create it for you.
Beware: if the file already exists, MySQL will overwrite it with the new session data.
4. Loading .CSV file data into a table using the command line
MySQL has a very simple way to load .csv file data into tables. Once you have the syntax down, I find this a lot easier and ess error-prne than commonly used web-based or GUI tools.
Syntax I’ve used is:
load data infile '/path/to/your.csv' into table my_tables fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Works a treat.
5. Cloning tables
This is really simple, but is something I use a lot to create development tables with live data (when necessary).
create table my_new_table as select * from my_old_table;
This does exactly what it looks like – creates an exact renamed copy of the original table (my_old_table in this instance).
Beware: keys and indexes aren’t recreated – you’ll need to add these back in manually.
What about you – any handy MySQL tricks to share?
mysqldump to text file
Posted on: August 4, 2008
Seeing as this is a bit of a pain to find, the correct syntax for exporting data and tables from a database is:
mysqldump -u username -p [database name] [table name] > [filename]
mysqldump will then ask you for your password and will then create the output file for you in the location your specify.
Note that mysqldump puts the file into the folder you’re executing from unless you specify otherwise.
Serf, Search API’s and secret sauce
Posted on: July 11, 2008
- In: API's | databases | semantic web | serf | web applications
- Leave a Comment
I’ve started building Serf – a personal project which I hope people will find useful. I’ve had plenty of ideas over the years but the idea behind Serf is something I find fascinating and feel compelled to pursue.
I’m going to keep it under wraps until it’s released (probably mid-October, if not sooner) but I can say it’ll involve hooking into some large API’s as well as some semantic web action. I’ll post notes as I can, as I’m sure I’ll be needing to solve some interesting problems along the way.
- In: databases | SQL
- 4 Comments
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 in 5.1. Great.
Turns out we’ll be able to skirt around this by making sdure this line is in the new my.cnf file:
old-passwords = 1
It’s great this was thought of, as PASSWORD() is a one-way, irreversible function, so authentication will fail for any old application using it when it is run on mysql >= 4.1.
thanks to [ducea.com]
————
Need to improve your MySQL skills? I recommend:
High Performance MySQL: Optimization, Backups, Replication, and More
- In: databases | SQL
- 13 Comments
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 to poor database design, the name field was in the form “lastname, first names”. Initially I used REGEXP to come up with something like this:
Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name REGEXP "^[ a-z]{0,},[ a-z]{0,}'.$firstName.'[ a-z]{0,}$"
and
table1.name REGEXP "^[ a-z]{0,}'.$lastName.'[ a-z]{0,},[ a-z]{0,}$"
and
table1.key = table2.foreignKey
Our testing showed that this query produced a good quality result for name searches, but I had a gut feeling it would slow the database server considerably when under a bit of load.
Testing using JMeter confirmed this hunch and I was faced with the task of optimising the query without affecting the quality of the results.
What to do? What would give the pattern-matching punch of REGEXP without killing the server?
Enter the LIKE function. I’ve used LIKE before but didn’t realise how powerful (and fast) it was. After a bit of tweaking I cam up with the following:
Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name LIKE "%,%'.$firstName.'%"
and
table1.name LIKE "%'.$lastName.'%,%"
and
table1.key = table2.foreignKey
I had (falsely) thought initially that LIKE‘s wild card characters (the “%” in the query) could only occur at the start or end of a query, but it turns out they can be used for all sorts of pattern matching goodness. The query using LIKE turned out the same results as the query using REGEXP, only it was at least 10 times faster.
The downside with LIKE‘s pattern matching (which didn’t affect my query) is that it’s wild card will match any character, whereas REGEXP can be tuned using normal regular expressions to providfe much more granular query control.
If you just need fast, ‘any character’ pattern matching though, LIKE is your friend.
mysql error #1064
Posted on: March 6, 2007
- In: databases | SQL
- Leave a Comment
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.
mysql error #1146
Posted on: March 6, 2007
MySQL server says: “I can’t get JOIN data from a table that DOESN’T EXIST. Do you hear me? YOUR TABLE NO EXISTO GENIUS!”
It’s hard to get data from a table that doesn’t exist – trust me on this one.
Either this, or you need to recreate your table and set the table type to MyISAM (which is a great name for a table type).
Note: Older versions of MySQL won’t show the error message when using a db management tool like phpMyAdmin – only this error number. This is where you play the ‘translate the error code for fun’ game.
paul bennett 
recent comments