bool / boolean datatype feature request (MySQL)

MySQL accepts bool and boolean as synonyms for TINYINT(1)

[ http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html ]

It would be great if HeidiSQL would add a section similar to "Logical" in the Datatype list and list bool and boolean there.


This would also be useful for MSSQL (haven't tried HeidiSQL with that, so maybe it exists.] and PostgeSQL [ http://www.postgresql.org/docs/9.2/static/datatype-boolean.html ] for which I hope you implement support soon!

;-)

Thanks for your consideration. HeidiSQL is one impressive tool!

Synonyms and all these silent changes in MySQL tend to act like lies for newbies. Once they see such "lies", users tend to rely on them and think it's a real feature in the underlying database. I always prefer to leave synonyms away from the GUI, but supporting the real data types, so especially newbies know what's going on in the server.

For the whole PostgreSQL idea, I'm currently comparing usage statistics against MySQL and MSSL. Just to help me deciding whether to start spending numerous hours for a new derivation of my quite complex TDBConnection class. Of course PostgreSQL is less popular than MySQL, but I wanted to know if I would go and support some kind of dying rdbms, which I still don't know yet. I just found this comparison, which surprised me a bit due to a relative high position of PostgreSQL on the market.

Hi Ansgar:


I understand your concern with synonyms. Keep in mind, however, that MySQL seems to be trying to meet ANSI SQL standards, eventually. Thus (I hope) at some point bool and/or boolean will actually be to define a true boolean field.

Also -- and this is important right now -- many texts suggest using bool or boolean (right now!) to define a TINYINT(1) for purposes of true/false fields. The DDL is then "futureproof" according to MySQL.

Also, when your tool does not support the full feature set of MySQL (and your competitors such as SQLYog and MySQL Workbench) then people get confused too.


RE: PostgreSQL -- usage apparently went up dramatically when Oracle acquired MySQL. [And many moved to MariaDB too.]

Personally I like aspects of PostgreSQL but really **hate** the layout of its datafiles. Why can't all databases at least offer the option to hide the datafile layout/complexity as does Firebird? [which generates a ONE file databases]

The problem with synonyms is that (as far as I know) MySQL silently discards the information. For example, if you have this table:

create table test (
age tinyint(1),
active bool
);

...

table_name

and

select *
from information_schema.columns
where table_name='test';

... with report that both columns have identical type: TINYINT(1). There's no way to tell which one was created as boolean.

Sorry, something went wrong and you can't edit posts. I meant that both

show create table test

and

select *
from information_schema.columns
where table_name='test'

will report TINYINT(1) in both cases.

will report TINYINT(1) in both cases.

I just tested this and found to my surprise that your comment (and ansgar's) are absolutely correct.

My impression was that MySQL preserved such DDL declarations (also foreign keys with myISAM tables, for example) so that the tables could be "properly" recreated with the users intended features when such become available in future versions of MySQL.

Thanks for pointing this out.