Rails Migrations and MySQL Types

February 8, 2007

The current Rails documentation is not very clear about how column types defined in migrations translate to MySQL types. When dealing with numerical calculations, precision is important. In certain circumstances, not just any old FLOAT will do so it is important to know what exactly :float translates to in the resulting MySQL table. In particular, I wanted to know what to do to get a MySQL DOUBLE. The current documentation (generated at Mon Feb 05 23:29:54 –0600 2007) claims that the :limit option does not apply to the :float column type. I couldn’t see any way to get a double if :float translated to the single precision FLOAT.

I performed the following simple test migration using Rails 1.2.1, ignoring the advice about the :limit attribute. While I was at it, I threw in a few :integer types and a :boolean.

class CreateTypes  ActiveRecord::Migration
  def self.up
    create_table :types do |t|
      t.column :float_default,   :float
      t.column :float_24,        :float,   :limit => 24
      t.column :float_25,        :float,   :limit => 25
      t.column :float_53,        :float,   :limit => 53
      t.column :integer_default, :integer
      t.column :integer_1,       :integer, :limit => 1
      t.column :integer_2,       :integer, :limit => 2
      t.column :integer_3,       :integer, :limit => 3
      t.column :integer_4,       :integer, :limit => 4
      t.column :integer_8,       :integer, :limit => 8
      t.column :boolean_default, :boolean

  def self.down
    drop_table :types

On MySQL 5.0.24a, this resulted in the following table.

CREATE TABLE `types` (
  `id` int(11) NOT NULL auto_increment,
  `float_default` float default NULL,
  `float_24` float default NULL,
  `float_25` double default NULL,
  `float_53` double default NULL,
  `integer_default` int(11) default NULL,
  `integer_1` int(1) default NULL,
  `integer_2` int(2) default NULL,
  `integer_3` int(3) default NULL,
  `integer_4` int(4) default NULL,
  `integer_8` int(8) default NULL,
  `boolean_default` tinyint(1) default NULL,
  PRIMARY KEY  (`id`)

So, it seems that it is possible to get a DOUBLE after all using a limit value between 25 and 53. This limit value corresponds to the precision of the column in bits. The behavior is in line with that outlined in the discussion of numeric types in the MySQL documentation. Precision values from 24 to 53 result in an eight-byte DOUBLE column.