Secrets of the MySQL Client Command Line, Page 2
Inserting Batch Data
Suppose you're writing an e-commerce application that will be used to sell a wide variety of products. It makes sense to categorize these products; therefore, you'll be using a table named categories to manage the category names:
mysql test>create table categories ( ->id integer not null auto_increment, ->name varchar(35) not null, ->primary key(id));
During breaks from writing the application, you've been adding to a textfile (categories.txt containing a list of categories. This file looks something like this:
Candy Fruit Coffee Tea Pop Vegetables
The time has come to add these categories to the categories table. You could do so manually, but this is time-consuming and error prone. The sane solution is to use MySQL's LOAD DATA INFILE command:
mysql>LOAD DATA INFILE '/home/jason/categories.txt' INTO TABLE categories ->LINES TERMINATED BY '\n'
If your input file has multiple items per row, you can tell MySQL to insert each into a separate column by delimiting them with a tab and using FIELDS TERMINATED BY '\t':
mysql>LOAD DATA INFILE '/home/jason/categories.txt' INTO TABLE categories ->FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
Disable the Annoying Error Beep
If you've ever seen the movie Dumb and Dumber, you might recall the scene where Lloyd (Jim Carrey) and Harry (Jeff Daniels) attempt to outdo each other in terms of who can make the most annoying sound in the world. The MySQL client's obnoxious error beep would have fared quite well in such a competition. If you're not familiar with it, just execute a malformed query from within the client for an earful. Although it has nothing to do with efficiency, I couldn't pass up the opportunity to work this tip for disabling it into this tutorial. To temporarily disable the beep, pass --no-beep along when logging into the server:
%>mysql -u root -p --no-beep
To permanently disable this annoyance, add no-beep to the [client] section of your .my.cnf file.
I hope this compilation of MySQL client "secrets" helps you use this powerful tool much more efficiently! If you'd like to share a tip, email me at jasonATwjgilmore.com!
About the Author
W. Jason Gilmore is a freelance web developer, consultant, and technical writer. He's the author of several books, including the best-selling Beginning PHP and MySQL 5: Novice to Professional, Second Edition (Apress, 2006. 913pp.).