What MySQL field should I use for a Yes/No Checkbox value?

Question

Hello,

I am using the MySQL table below. I would like to add a field called 'subcheck' that will be a yes/no value determined by the HTML form input TYPE = CHECKBOX. What "type" should I give this new field?

Thanks in advance,

John

`submission` (
  `submissionid` int(11) unsigned NOT NULL auto_increment,
  `loginid` int(11) NOT NULL,
  `title` varchar(1000) NOT NULL,
  `slug` varchar(1000) NOT NULL,
  `url` varchar(1000) NOT NULL,
  `displayurl` varchar(1000) NOT NULL,
  `datesubmitted` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`submissionid`)
)

Answer 1

a boolean - 1 for yes, 0 for no.

(value of the checkbox will need to be 1 or 0 of course).

Much more portable than yes/no imo. efficient too

Answer 2

I would recommend a TINYINT(1) for this purpose. It stores either 1 or 0, to indicate yes or no. It takes very little space and is better supported across different SQL-engines than regular bool-types.

Answer 3

I would use numeric(1) not null default 0 wherein 0 indicates false and 1 indicates true.

Answer 4

You can use a TINYINT(1) (BOOL/BOOLEAN is just an alias for TINYINT(1)).

Another option is to store Y/N in a CHAR(1).

I would recommend TINYINT(1) as it would give you best portability options.

Answer 5

I personally prefer a enum or set data type in MySQL for this. It keeps the data legible.