Rails 深入学习 Migration limit

Posted by wxianfeng Sat, 16 Apr 2011 21:09:00 GMT

环境:ruby1.9.2 + rails 3.0.3

一直以为

add_column :users , :age  ,  :integer  , :limit=> 4  

在数据库里对应的类型是 int(4)

其实是错误的!!!

看下 limit的说明文档:

:limit - Requests a maximum column length. This is number of characters for :string and :text columns and number of bytes for :binary and :integer columns.

对于 string 和 text 比较简单,例如

add_column :users,:name , :string , :limit=> 60 

那么数据库中的 类型就是 varchar(60)

对于 binary 和 integer 的就不一样了 , 表示的是字节数 , 但是 :limit =>11 不是表示 11个字节的整数 , 是4 个字节整数

对应关系:

:limit          Numeric Type          Column Size
1                      tinyint                  1 byte
2                      smallint                  2 bytes
3                      mediumint             3 bytes
nil, 4, 11      int(11)                  4 bytes
5 to 8              bigint                  8 bytes

而mysql的integer类型(也是int型) 表示大小如下:

详细 here

rails 里的实现代码 here

核心代码:

 # Maps logical Rails types to MySQL-specific data types.
      def type_to_sql(type, limit = nil, precision = nil, scale = nil)
        return super unless type.to_s == 'integer'

        case limit
        when 1; 'tinyint'
        when 2; 'smallint'
        when 3; 'mediumint'
        when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
        when 5..8; 'bigint'
        else raise(ActiveRecordError, "No integer type has byte size #{limit}")
        end
      end

从上面代码可以看出, 当limit 为 nil,4,11 的时候 , mysql的类型就是 int(11), 也就是常在 migration看到的 integer , :limit=>11

另外可以从已经有的表中得到字段的字节数

ruby-1.9.2-p0 > ActiveRecord::Migration.add_column :forms , :int9, :integer , :limit=>11
-- add_column(:forms, :int9, :integer, {:limit=>11})
  SQL (300.9ms)  ALTER TABLE `forms` ADD `int9` int(11)
   -> 0.3012s
 => nil 
ruby-1.9.2-p0 > Form.reset_column_information
 => nil 
ruby-1.9.2-p0 > Form.columns_hash["int9"].limit 
 => 4 
ruby-1.9.2-p0 > Form.columns_hash["int9"]
 => #<ActiveRecord::ConnectionAdapters::Mysql2Column:0xb8407c8 @null=true, @sql_type="int(11)", @name="int9", @scale=nil, @precision=nil, @limit=4, @type=:integer, @default=nil, @primary=false>

看到没,在迁移的时候 指定 :limit => 11 , 但是 通过 columns_hash 得到的 limit 确是4 , 也就是说 mysql 的 Column Size 是4 bytes , 如果指定 :limit => nil 或者 :limit=>4 得到的 都是 4 bytes

因为 rails 里面 主键id 默认是有符号的 int(11) ,所以 mysql的主键最大id 是 2147483647 , 如果改成无符号的 最大可以到 4294967295

ruby 如何得到 整数的字节数?

size 方法

ruby-1.9.2-p0 > 10_000_000_000.size
 => 8 
ruby-1.9.2-p0 > 1.size
 => 4 

如何让我的integer类型的字段变成无符号的?

ruby-1.9.2-p0 > ActiveRecord::Migration.add_column :forms , :int8, "integer unsigned"
-- add_column(:forms, :int8, "integer unsigned")
  SQL (297.4ms)  ALTER TABLE `forms` ADD `int8` integer unsigned
   -> 0.2976s
 => nil 

如何正确 添加mysql中int(4)类型的字段?

ruby-1.9.2-p0 > ActiveRecord::Migration.add_column :users , :number , "int(4)"
-- add_column(:users, :number, "int(4)")
  SQL (280.4ms)  ALTER TABLE `users` ADD `number` int(4)
   -> 0.2808s

最后看下 migration 类型 对应 数据库类型的关系:

SEE:

http://www.snowgiraffe.com/tech/366/rails-migrations-mysql-unsigned-integers-primary-keys-and-a-lot-of-fun-times/
http://thewebfellas.com/blog/2008/6/2/unsigned-integers-for-mysql-on-rails
http://www.kuqin.com/rubycndocument/man/built-in-class/class_object_numeric_integer.html

This entry was posted on Sat, 16 Apr 2011 21:09:00 GMT and Posted in . You can follow any any response to this entry through the Atom feed. You can leave a comment or a trackback from your own site.

Tags


Trackbacks

Use the following link to trackback from your own site:
http://wxianfeng.com/trackbacks?article_id=200

Comments

  1. Avatar
    Passenger ships Bari Patras over {{count}} years ago:

    Excellent post. Keeep writing such kind of info on your blog.

    Im really impressed by it.
    Hi there, You have done a great job. I’ll definityely digg iit and
    iin mmy view recommend to my friends. I’m conmfident they
    will be benefited from this web site.

  2. Avatar
    Passenger ships Bari Patras over {{count}} years ago:

    Excellent post. Keep writing such kind of info on your blog.

    Im really impressed by it.
    Hi there, Yoou have done a great job. I’ll definitely
    diugg it and in my view recommend to my friends.
    I’m confident they will be benefited from this web site.

Leave a comment