Create a table:

   1  create table page_views (id VARCHAR(50), views INTEGER) primary key id;

Use the ON DUPLICATE declaration:

   1  insert into page_views (id, views) values ('/', 1) on duplicate key update views = views + 1;

Idea from http://simonwillison.net/2009/Jun/30/analytics/

Listing table information including collation

   1  show table status;

To show all table information use this command:

   1  show full columns from xxx;

Convert existing columns in a table to a specific collation

   1  alter table brands convert to character set utf8 collate utf8_swedish_ci;

This command converts all existing columns to the specified collation.

Change table collation

   1  alter table posts collate utf8_swedish_ci;

Note that this doesn’t change collation of existing columns.

Setting the collation manually

   1  set session collation_database=utf8_swedish_ci;
   2  set session collation_connection=utf8_swedish_ci;

Common collation values

   1  utf8_swedish_ci
   2  utf8_general_ci

The column must become a key column.

   1  alter table my_table add id int primary key auto_increment;

You can use the model.update_counters method to update the counter_cache column. But if you have a million rows it be very fast.

So for large tables it’s best to do it with a query such as this:

   1  update categories, (select 
   2                        id as category_id, ifnull(count, 0) as count
   3                      from categories left join 
   4                        (select category_id, count(id) as count from products group by category_id) as count_table 
   5                      on 
   6                        categories.id = count_table.category_id) as count_table
   7  set 
   8    categories.products_count = count_table.count
   9  where
  10    categories.id = count_table.category_id

This query updates the count for all rows.

The code needs to be modified for your database design.

This is a simple example of a MySQL stored procedure that has both an in and out parameter:

   1  DELIMITER |
   2  DROP PROCEDURE IF EXISTS category_for |
   3  CREATE PROCEDURE category_for (product_id int, OUT r_category_id INT)
   4   BEGIN
   5    DECLARE category_id INT;
   6    DECLARE products_cursor CURSOR FOR SELECT category_id FROM products where id = product_id;
   7    OPEN products_cursor;
   8     FETCH products_cursor INTO category_id;
   9    CLOSE products_cursor;
  10    SET r_category_id 	= category_id;
  11   END |
  12  DELIMITER ;

Note that we use ; inside the procedure so we have to set the delimiter to | temporarily.

To call the stored procedure use the following SQL commands:

   1  CALL category_for(202, @category_id);

The @category_id session variable will now contain the procedure’s output:

   1  select @category_id;
   2  +-------+
   3  | @category_id |
   4  +-------+
   5  | 3     | 
   6  +-------+