CODEWITHSUNDEEP
phpmysqlsqldatabase
Programmer
Programmer

Reputation: 1294

What data type can be used to put a flag (boolean) in mysql?

I have a database in which I have the following columns: 

emp_id: INT(11) (PRIMARY KEY)
FirstName: varchar(45)
LastName: varchar(45)
password: varchar(45)
admin: ????

I want the admin column to be a flag, so the employee is either an admin (true) or not an admin (false). Can someone please tell me what datatype to assign to this column in the mysql table?

Upvotes: 2

Views: 1812

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

You should be able to declare the column as TINYINT NOT NULL DEFAULT FALSE (or DEFAULT 0).

An insert would be VALUES (TRUE, ... or VALUES (FALSE, ... or VALUES(1, ... or VALUES (0, ....

As an unquoted literal, TRUE is equivalent to 1 and FALSE is equivalent to 0, because there is no pure Boolean data type in MySQL.

Your application will always see the column as containing 1 or 0 in result sets.

Disregard the number in parentheses, e.g. TINYINT(1) or TINYINT(4). That's part of a legacy feature that provided hints to applications for how wide the value stored in a column would be expected to be... it was useful for fixed-width fonts on display terminals, but is largely ignored by applications today, and this value changes nothing about the contents of the column or the storage requirements for it. A TINYINT requires 1 byte of storage per row.

Using an ENUM isn't typically recommended for the boolean case, because behind the curtain, enums have numeric values that can cause logical ambiguities, since the "string" for a value will be cast implicitly to it's integer index (1-based, not 0-based) in some contexts.

Upvotes: 4

Aref Anafgeh
Aref Anafgeh

Reputation: 512

use ENUM type.enum can have values that you define and cant accept any values except you define for it.this is example

 enum('DENYED','NOT_ACCEPTED','ACCEPTED') COLLATE utf8_persian_ci DEFAULT 'NOT_ACCEPTED'

Upvotes: -1

Related Questions