Archive

Archive for the ‘MySQL’ Category

MYSQL ALTER Statement for TIMESTAMP Column to Change on Update

January 7th, 2011 Comments off

If you need to alter a TIMESTAMP column to update the column automatically when the contents of the row are changed, run the following MySQL ALTER statement against the table: Read more…

Categories: MySQL Tags: , ,

Quickly duplicating MySQL table structures

May 1st, 2009 Comments off

I often find myself duplicating table structures to either do some testing or duplicating a database structure from one database to another. Most of the time,  I find that I do not need to copy the existing data to the new database table. The easiest way I have found to do this is using MySQL’s CREATE TABLE…LIKE… () syntax. Even the most basic form can fulfill most requirements. This form could be something like:

CREATE TABLE `bar` LIKE `foo`;

For the MySQL 5.1 reference page, go here:  http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Categories: MySQL Tags: ,

Selecting a result set into a Comma Seperated Value (.csv) file

April 14th, 2009 Comments off

Getting a result set from MySQL into a comma seperated value (.csv) file does not have to be a difficult adventure.  In the past, I might have written my query in SQLyog, select the query, hit F9 to run the query, and click on the button to Copy All Rows to Clipboard, paste the copied results into an Excel spreadsheet and save the Excel spreadsheet at a .csv file. Trust me though, there is a better way!

To save a result set into a .csv file from your query, check out MySQL’s SELECT…INTO OUTFILE syntax. In the following example, I want to create a comma seperated value file that contains a list of all cities in the state of Michigan from my cities table. The syntax to do so, on my Windows machine, would look like:

SELECT     city, state_abbrev, country_code
INTO OUTFILE 'C:\temp\cities.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM     cities
WHERE     state_name = 'Michigan'
ORDER BY city ASC

To get more information on this nifty feature of MySQL, check out the SELECT…INTO OUTFILE manual page (http://dev.mysql.com/doc/refman/5.0/en/select.html) at mysql.org

MySQL: Order By NULL

April 9th, 2009 Comments off

I amaze myself sometimes. For a long time now, I really never put two and two together about MySQL query performance and the fact that MySQL, by default, does a sort on the returned result set. Now, I have not seen any documentation on how MySQL chooses the order by column it chooses.  Neither did I realize that simply adding an ORDER BY NULL clause at the end of queries you do not need sorted, could increase query performance by quite a bit. I have not done any hardcore benchmarking against this, but there I experienced about 65% performance increase in a couple queries that returned 500,000 rows.

Categories: MySQL Tags: