How to create a column of type tinyint(2) or tinyint(3) in Ruby on Rails?

Question

In Ruby on Rails, the following code in a migration creates a column of type tinyint(4) in MySQL:

create_table :great_table do |t|
    t.integer :step_position, :limit => 1 #tinyint
end

How would I create a column of type tinyint(2) or tinyint(3)?

I'd discourage you from using `tinyint` in the first place. It's overly-specific. I would venture to guess that it's actually LESS performant than using a plain integer field, as well. It might save you a negligible amount of space per-record, but I'm not even sure that's the case. I think you'd be hard-pressed to convince me that there was a reason to ever use it. — Jim Stewart, Jan 30 '14 at 02:33

score 23 · Answer 1 · answered Sep 15 '14 at 19:17

23

For tinyint(2)

create_table :great_table do |t|
  t.integer :step_position, :limit => 2
end

For tinyint(3)

create_table :great_table do |t|
  t.integer :step_position, :limit => 3
end

answered Sep 15 '14 at 19:17

srbhattarai

792
7
9

That does not create tinyints. A tinyint uses 1 byte for storage by definition. Here you're using 2 bytes (smallint) and 3 bytes (mediumint). https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – TanguyP Jan 14 '20 at 14:35

score 12 · Answer 2 · answered Jan 30 '14 at 02:31

12

According to what I can see in the source code of the gem, you can't:

     # File activerecord/lib/active_record/connection_adapters/mysql_adapter.rb, line   540  
     540:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
     541:         return super unless type.to_s == 'integer'
     542: 
     543:         case limit
     544:         when 1; 'tinyint'
     545:         when 2; 'smallint'
     546:         when 3; 'mediumint'
     547:         when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
     548:         when 5..8; 'bigint'
     549:         else raise(ActiveRecordError, "No integer type has byte size #{limit}")
     550:         end
     551:       end

type_to_sql

answered Jan 30 '14 at 02:31

FedeX

426
2
8

Not sure this is a relevant snippet of code; what's in the superclass for this? Migrations do support a `:tinyint` type. This snippet of code is where Rails decides to ignore your `:integer` type specification and use an alternate type to represent smaller/larger integer values when both `:integer` and `:limit` are specified. – Jim Stewart Jan 30 '14 at 02:36
Afraid you are wrong, type_to_sql is called from add_column. You can see that in the code: http://api.rubyonrails.org/v2.3.8/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html#M001549 – FedeX Jan 30 '14 at 02:42
I think I may be wrong about there being any support for a tinyint type in the first place (other than indirectly due to an integer and limit combo). The code above is certainly applied when the type is integer. – Jim Stewart Jan 30 '14 at 03:01

score 5 · Answer 3 · answered Oct 26 '17 at 08:30

There is no such thing as tinyint(4) in MySQL in the first place. tinyint is a one byte signed integer. You can check all integer types in the docs. You may see something like tinyint(1) even in the Rails source code, but I think it's a tautology as tinyint already implies one byte storage.

The Rails way to declare TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT in a migration is by using limit: with the appropriate byte size as can be seen in the source code.

Beware that Rails will treat one-byte integers as booleans by default though, as can be seen from the above link.

Actually, the number between parentheses has nothing to do with the number of bytes used for storage. It just specifies a display width, which applications displaying this number may or may not choose to honour. https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html — TanguyP, Jan 14 '20 at 14:29

score 1 · Answer 4 · answered Mar 17 '17 at 16:41

1

You can use :tinyint as the column type in a rails migration, and write limit: 2 or limit: 3 as written before.

Using your example:

create_table :great_table do |t|
    t.tinyint :step_position, :limit => 1
end

should work.

answered Mar 17 '17 at 16:41

adamszkly

31
3

2

Hey buddy, You cannot use tinyint in migration, infect it should have integer and limit 1 – Gagan Sep 22 '17 at 13:48
1

@Gagan Providing `add_column :table_name, :column_name, :tinyint` doesn't raise any exception and seems to go perfectly fine through the migration. I can imagine `t.tinyint` working in the same way. It could be that this is since the newer versions though, I've tested adding a tinyint column in Ruby on Rails 5.1. – 3limin4t0r Mar 04 '19 at 14:27

How to create a column of type tinyint(2) or tinyint(3) in Ruby on Rails?

4 Answers4