CODEWITHSUNDEEP
mysqlsqldatabaserdbmssqldatatypes
Drewdavid
Drewdavid

Reputation: 3192

What Data Type to Store 0, 1, -1 values only?

I tried creating a field as a TINYINT(1), NOT NULL and a DEFAULT value of -1 to indicate 'unknown', but I got this error from my client:

Error altering MyTable: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I also tried making the length 2 and got the same message

Guessing TINYINT isn't the right data type for this kind of designation, but what is?

EDIT: I got it working after reading Ed Cottrell's comment. I think there was an extra character somewhere, here was the statement that my client (using Querious for Mac) generated:

ALTER TABLE `DBName`.`MyTable`
CHANGE COLUMN `MyColumn` `MyColumn` TINYINT(1) NOT NULL DEFAULT -1  COMMENT ''
AFTER `MyOtherColumn`;

Noticed that COMMENT there and made sure everything was clean.

Other comments and answers were appreciated; I have decided to let NULL mean unknown in this case

Upvotes: 0

Views: 4744

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I think you should store this as a bit (if you care about storage size), and let NULL mean "unknown".

field bit(1) default NULL,

It seems strange to declare a field to be not null and then to have a special value that, essentially, means NULL.

EDIT:

The following syntax "works" on SQL Fiddle:

create table t (
  val int,
  flag tinyint(1) default -1
 );

"works" is in quotes because the default value prints as "1" rather than "-1" -- after all, the (1) is saying just print one digit.

Perhaps in some earlier versions of MySQL it generates an error when it sees that -1 won't display correctly. (To be honest, that would surprise me.)

Upvotes: 3

Related Questions