Noob Question - Migrations

Hello list

If I am using migrations to build my Db schema, is there any way to generate fixed-length fields for MySQL (i.e. char instead of varchar). I have tried STFW, but have come back with nothing to say that it is possible, but nothing that conclusively states it is impossible.

Thanks

t.column :fieldname, :string, :limit => 10

Ryan Bigg (Radar) wrote:

t.column :fieldname, :string, :limit => 10

<snip>

Thanks Ryan

But if I read my docs correctly - wouldn't that create a VARCHAR(10)? When what I need is a CHAR(10).

Regards

Rory

I just tried doing t.column :fieldname, :char, :limit => 10 and it worked for me, you might as well give it a shot too.

Ryan Bigg (Radar) wrote:

I just tried doing t.column :fieldname, :char, :limit => 10 and it worked
for me, you might as well give it a shot too.

<snip>
Hi Ryan

Unfortunately, it didn't work for me:

A migration script that looks like this:

create_table(:users, :options=> 'ENGINE MyISAM DEFAULT CHARACTER SET UTF8') do |t|
         t.column :first_name, :char, :limit => 10, :null => false
         t.column :last_name, :char, :limit => 10, :null => false
         t.column :permission_goal_super, :boolean, :null => false
         t.column :permission_milestone_super, :boolean, :null => false
     end

Produces a table that looks like this:

CREATE TABLE `users` (
   `id` int(11) NOT NULL auto_increment,
   `first_name` char(1) NOT NULL,
   `last_name` char(1) NOT NULL,
   `permission_goal_super` tinyint(1) NOT NULL,
   `permission_milestone_super` tinyint(1) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

It seems that if you use :char you are limited to exactly that - a character ;).

One workaround I have found is to manually add character columns post creation using the execute method.

Why must it be a char field type? surely varchars are just as good, unless it’s a life-or-death situation to making your database as small as possible.

Ryan Bigg (Radar) wrote:

Why must it be a char field type? surely varchars are just as good, unless
it's a life-or-death situation to making your database as small as possible.

<snip>
Hi Ryan

Actually, varchars in mysql will on average use less space than a char field, unless you are maxing out your field length every time. But, varchars infer performance penalties when used as part of an index. From a DB design POV, it is a good idea to minimise the use of varchars.

I worked around, by using execute - works like a charm!

Fair enough. Glad to see you found your own solution. :slight_smile: