Export MySQL query result to a CSV file

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

http://tlug.dnho.net/node/209
http://dev.mysql.com/doc/refman/4.1/en/select.html

Another example:

SELECT v.id,v.name,t.name,d.name INTO OUTFILE '/root/tr_villages.csv' 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  FROM villages v JOIN taluqs t ON v.taluq_id=t.id JOIN districts d ON t.district_id=d.id 
  WHERE t.district_id IN (163,164,165,166);

Duplicating tables and problem with the structure

To duplicate a table:
mysql> CREATE TABLE products SELECT * FROM old_products;

But the problem with above statement is structure of products may not be the same as old_products. Attribute properties are not copied to the new table. For example of you have an auto_increment field named id, the auto_increment property won't be copied to products table. products.id will be a normal integer field with the default value set to 0. It also means the new id field won't be the primary field. Took me 3 hours to figure this out.

So the best way to duplicate a table is:
1. CREATE TABLE products LIKE old_products;
2. INSERT INTO products SELECT * FROM old_products;

Common MySQL stuff

To modify an existing column definition:

ALTER TABLE applications modify id INTEGER NOT NULL AUTO_INCREMENT FIRST;

To rename a column:

ALTER TABLE applications CHANGE old_col new_col;

Find out mysql socket

1. Enter mysql command line with 'mysql -u root -p'
2. mysql> mysql status
   

Duplicating a table in MySQL

CREATE TABLE duplicate_name SELECT * FROM original_table;

Column type mappings for databases

I always wondered how Rails maps the column types we define in migrations to appropriate data types in the database. There is no direct mapping between the data types that Ruby defines and the ones a database defines (not all at least). For example there is no string data type in any of the databases that I have worked with. I figured that today.

Here is a hash defined by mysql_adapter.rb:

      def native_database_types #:nodoc:
        {
          :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
          :string      => { :name => "varchar", :limit => 255 },
          :text        => { :name => "text" },
          :integer     => { :name => "int", :limit => 11 },
          :float       => { :name => "float" },
          :decimal     => { :name => "decimal" },
          :datetime    => { :name => "datetime" },
          :timestamp   => { :name => "datetime" },
          :time        => { :name => "time" },
          :date        => { :name => "date" },
          :binary      => { :name => "blob" },
          :boolean     => { :name => "tinyint", :limit => 1 }
        }
      end

If you are using a different database then look up this hash in its respective adapter. Normally adapters are named like "#{database}_adapter.rb" under '\active_record\connection_adapters".

A detailed look at Ruby Vs MySQL mappings:
http://www.orthogonalthought.com/blog/index.php/2007/06/mysql-and-ruby-on-rails-datatypes/