View Full Version : yes/no data type

04-11-2007, 03:44 AM
Does this exist in mySQL?

04-11-2007, 05:57 AM
Use the field type "enum" with two options being specified as 'y' and 'n'. So, in a sense, yes, the yes/no data type is supported.

But the values in an enum field don't HAVE to be 'y' and 'n'. It can be 't' & 'f' (true/false), or anything else your imagination takes you.

Hope this helps,

04-11-2007, 02:06 PM
Although ENUM does exist, you are better off setting it to a TINYINT(1) UNSIGNED field, using 1=true/yes and 0=false/no. Smaller table size and (slightly) faster indexes.

ENUM data type: http://dev.mysql.com/doc/refman/5.0/en/enum.html

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT data types: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html


04-11-2007, 03:05 PM

and IF you use ENUM, don't use values like y or n or things like that, use 1 and 0 or true and false

04-11-2007, 03:42 PM
Don't bother using ENUM, use tinyint as suggested. There are no real benefits from using ENUM and there are many flaws with it.

If you set up ENUM to use 'T' and 'F' for instance you can insert 'Scribble' if you want. It doesn't get stored as T/F or scribble, nor does it get stored as a NULL.

04-11-2007, 09:38 PM
Thank for the replies, I've set it up as

`Garden` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
`Garage` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
`Driveway` tinyint(1) NOT NULL COMMENT 'Option: YES/No',

Which i assume is correct.

I then converted an access database to MySQL for one of my friends, and as he already had values in these 3 fields, it has come up using 1 and 0 instead of YES or No..does this matter? Obviously Yes/No would be preferred for when i echo these fields onto a php page.

I'm kind of new to MySQL and I'm used to using constraints for Yes/No data types

04-11-2007, 10:05 PM
Your table definition is a little off. You cannot have a NOT NULL value without setting the DEFAULT.

`Garden` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
`Garage` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
`Driveway` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',

I set your DEFAULT = 1 because you have YES in uppercase. Set the DEFAULT = 0 for a NO value.

I am assuming that you are using phpMyAdmin, as it would have set your DEFAULT = '' rather than give you an error.

When converting MSAccess to MySQL, Access sets Yes/No = 1/0. TINYINTs will only store integers, not string values 'Yes' and 'No'.

As to your PHP echo, just use

IF(value == 0) echo 'No'
IF(value == 1) echo 'Yes'

*Not a PHP programmer, please use the correct syntax in your app.


04-11-2007, 10:14 PM
I see what you mean, thanks.

The uppercase YES is only in the comment though so that doesn't affect anything.

Kinda annoying tinyints don't store string values

04-11-2007, 10:22 PM
TINYINT = Tiny Integer, -64 to 64 or UNSIGNED (not negative), 0 to 127.

That's why it's called that. If you want to use 'Yes' and 'No', then you would be better off changing the fields to VARCHAR(3)

VARCHAR = Variable Character, length of 3.

Differences is that TINYINT can have an index, making searches faster, while VARCHAR(3) cannot, as indexes on fields must have a minimum length of 4.

Believe it or not, Access stores YES/NO as TINYINT(1) UNSIGNED, but the front-end client shows you YES/NO. It's just a display thing.

04-12-2007, 04:29 AM
TINYINT = Tiny Integer, -64 to 64 or UNSIGNED (not negative), 0 to 127.

Actually unsigned tinyint holds values of 0 to 255 and signed values of -128 to 127. Not sure where you got those other values from.

04-12-2007, 04:30 AM
Kinda annoying tinyints don't store string values
TINYINT is a numeric storage type, hence it doesn't store strings. Take a look at the various data types in the MySQL resources thread at the top of this forum.

04-12-2007, 05:03 AM
Not thinking. That's where I got it from. I had 127 as the max stuck in my head.

ralph l mayo
04-12-2007, 05:19 AM
ENUM('Yes', 'No') is a better solution to this problem. MySQL itself uses ENUM('Y', 'N') internally. It's stored numerically internally and takes 1 byte total to store and index, just like TINYINT, and it lets you name the values like the OP seems to want. It's more obviously boolean so its meaning is more discoverable.