Bug- Alter Table boolean fields

[Harry711]

I forgot to add version to previous post , I'm using ver 4.03

[Ritesh]

Just press Del when the focus is in the len column.

[Ritesh]

SQLyog v4.05 BETA 3 will automatically delete the length value for datatypes not supporting length.

[seanhogge]

I did a search, and only found two posts regarding this problem, so forgive me if this is not the best place to mention this.

I'm using v4.05, and I am adding a column to a table. For the life of me I cannot make it add a bool type column. It converts it to a tinyint(1). Is there something I'm missing? I wouldn't put it past me to be doing something obviously wrong (it's late, I've been working for a while).

Thanks for your assistance and for what is, overall, a brilliant product.

[Ritesh]

In MySQL, tinyint(1) is a synonym for BOOL.

Check http://dev.mysql.com/doc/mysql/en/numeric-types.html for more details.

[seanhogge]

Alright. That's embarrassing. I just assumed – well, you know the rest.

Thanks for replying so quickly. Good morning!

[peterlaursen]

Wrong Ritesh!

See

http://dev.mysql.com/doc/mysql/en/numeric-types.html

Tinyint is a 1-byte integer, ie. it ranges from -128 to 127

Tinyint(1) is wide used a a replacement for a boolean and the ability to set it for instance (-1) can be very usefull. But it IS not a boolean!

did you confuse it with a “bit” type ?

“As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) In MySQL 5.0.3, BIT is supported only for MyISAM. MySQL 5.0.5 extends BIT support to MEMORY, InnoDB, and BDB.”

[seanhogge]

I think Ritesh was correct as far as MySQL's changing of BOOL to TINYINT(1) based on the following link:

http://dev.mysql.com/doc/mysql/en/other-ve…lumn-types.html

I agree that is is definitely not the same data type in a classical sense, though. As long as it's easy to store what is essentially a 1 or 0 (true/false) value it suits my purposes. I suppose it might cause conflicts in code that doesn't use stored procedures (such as they are in MySQL 5).

[peterlaursen]

Ritesh was not right …

.. but as I wrote it is commonly used to use a tinyint as a substitute for a boolean. With a tinyint you can for instance use values -1, 0 and 1, and that gives the opportunity to let -1 have a special meaning.

One appplication that makes use of this is the popular forum software phpbb2. With the config-table of this application 1 means true, 0 means false and -1 means “use defaults”. I believe that this also is a “workaround” that is used by this software so that it will run with the same code on more database-server systems.

But a tinyint(4) is NOT a boolean. Value could be “-127” ….

[seanhogge]

I see now. I didn't think he was saying that a TINYINT(1) is precisely the same as BOOL, only that their function was similar. It was early in the morning, maybe it was pre-coffee.

[peterlaursen]

no bad feelings …

the point is that a tinyint works fine and seamlessly as a boolean, so if you use like that you just don't think about it after a while … 😀

[seanhogge]

I've been limited on database choices since I began doing this stuff. I'm only now coming into my own hardware and software choices, so these minor details are excellent to debate.

[Ritesh]

Quote:

As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1)

Probably he was using MySQL version < 5.0.3 😀

[Author]

Posts