Boolean Values in SQL

Some stuff I don't understand.

[Nick_W]

I've just been reading this page on MySQL Column Types [mysql.com] and in particular TINYINT(1) as a boolean value.

I don't get it though. If I specify TINYINT(1) UNSIGNED NOT NULL then I seem to be able to put any unsigned value from 0-255 in it.

I must be missing te concept. I need to have a column with a 'yes/no' or '0/1' value in it so this seems right but I must be missing somthing.

Could anyone offer the 'idiots explanation' as to the workings and value of this TINYINT(1) thingy?

Many thanks ;)

Nick

[jdMorgan]

Nick,

Just looking at that page, it seems that tinyint is an 8-bit value which can be signed or unsigned, and that it is the smallest unit supported by MySQL for column types.

If they follow industry standards, then when using tinyint as a boolean, a value of zero would be "false" and any other value would be "true."

This is based only on my previous career and on the page you cited - I know nothing about MySQL.

HTH,
Jim

[Paul in South Africa]

Nick,

Jim is right in his remark that tinyint is an 8-bit value thus it gives you 2^8 (256) possible values ie 0 to 255 when unsigned.

[Nick_W]

Ok, I'm with you both on that bit. But I don't understand the syntax and what it means:

TINYINT(2) // for example

What does that mean? - As far as I can see, specifying a 'maximum display' number (2 in this case) has no effect....?

Many thanks

Nick

[andreasfriedrich]

Specifying a display width is for formatting only. It does not add any constraints on the allowed data. I recommended it nevertheless since it will remind you that all you want is a BOOL [mysql.com]ean or BIT [mysql.com] value. MySQL [mysql.com] just has no column type that is smaller and will only take up one bit.

Another extension is supported by MySQL [mysql.com] for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004.

Numeric Types [mysql.com] (my emphasis)

Andreas