Read more

MySQL: How to create columns like "bigint" or "longtext" in Rails migrations, and what :limit means for column migrations

Arne Hartherz
March 15, 2013Software engineer at makandra GmbH

Rails understands a :limit options when you create columns in a migration. Its meaning depends on the column type, and sometimes the supplied value.

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

The documentation Show archive.org snapshot states that :limit sets the column length to the number of characters for string and text columns, and to the number of bytes for binary and integer columns.

Using it

This is nice since you may want a bigint column to store really long numbers in it. You can just create it by saying integer, :limit => 8 and you'll be able to store 8 bytes of data.
Similarly, you can use it to get a mediumtext or longtext column by applying the proper limits Show archive.org snapshot (65535 bytes would be a text, up to 16777215 a mediumtext, and up to 4294967295 a longtext).

Or if you know that you'll never store more than a certain value, you might want to choose smaller column sizes for performance (it won't matter until your project becomes big, so don't worry about it for now).

Integer column lengths, mediumint, bigint, and all that other stuff

Mind that some values have a special meaning for integer Show archive.org snapshot :

create_table 'example' do |t|
  t.integer :int                 # int (4 bytes, max 2,147,483,647)
  t.integer :int1, :limit => 1   # tinyint (1 byte, -128 to 127)
  t.integer :int2, :limit => 2   # smallint (2 bytes, max 32,767)
  t.integer :int3, :limit => 3   # mediumint (3 bytes, max 8,388,607)
  t.integer :int4, :limit => 4   # int (4 bytes)
  t.integer :int5, :limit => 5   # bigint (8 bytes, max 9,223,372,036,854,775,807)
  t.integer :int8, :limit => 8   # bigint (8 bytes)
  t.integer :int11, :limit => 11 # int (4 bytes)
end

The first case is probably not surprising, it's just the default: when you say integer :my_column, you just get an int, or int(11) since its maximum value has 11 digits. You'll get the same when saying :limit => 4 just because an int holds up to 4 bytes of data.

However, mind that while you might think saying :limit => 11 gives you something like a superbigint or at least an exception, it will in fact create an int column, which is smaller than a bigint.


These are values for MySQL. At least on PostgreSQL you can just use bigint instead of integer:

create_table :example do |t|
  t.integer :int             # int
  t.integer :int2, limit: 2  # smallint
  t.bigint :int8             # bigint
end
Posted by Arne Hartherz to makandra dev (2013-03-15 09:16)