IIIFormed

Speed up your database manipulation and population. Using the command line there are many time saving methods...

These are a few of the time savers i have picked up using mysql through the command line. If you know what your doing then this is still the most effective way of getting things done quickly!

MYSQL Find and Replace

A handy time saver. To find a string in a certain field and replace it with another string:

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Using the LOAD DATA INFILE SQL statement

The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the MySQL prompt:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.

Recommended

Not only is MySQL the world's most popular open source database, it's also become the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) MySQL runs on more than 20 platforms including Linux, Windows, OS/X, HP-UX, AIX, Netware, giving you the kind of flexibility that puts you in control.

Further Info

MYSQL Database Server

3Formed Design, UF 44 Lower Market St, Hove, East Sussex, BN31AT T: +44 (0)1273 204216 F: +44 (0)1273 204216 E: info@3formed.co.uk VAT Registered No. 913 3804 42