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

Question

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

I believe this answer will work for you. http://stackoverflow.com/questions/4401673/mysql-boolean-tinyint1-holds-values-up-to-127 — gpopoteur, Nov 07 '13 at 22:50
Maybe you should use nullable field, and set `null` as *unknown*? — MarcinJuraszek, Nov 07 '13 at 22:51
This sounds like an actual typo; can you post the full `ALTER` statement? — elixenide, Nov 07 '13 at 22:53
@EdCottrell I added the statement to the question, thanks :) — Drewdavid, Nov 07 '13 at 22:58
Hi Ed, you were right somehow some kind of "comment" made it's way in there — Drewdavid, Nov 07 '13 at 23:09

Gordon Linoff · Accepted Answer · 2013-11-07T23:12:52.263

3

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.)

edited Nov 07 '13 at 23:12

answered Nov 07 '13 at 22:52

Gordon Linoff

1,242,037
58
646
786

As an aside, I notice you haven't put a field length in the BIT field in your statement, is this not necessary? Also, I don't think I'll get any storage savings if it's my only BIT column (which it happens to be in this table); I believe BIT takes up 1 byte for up to 8 columns, and TINYINT also takes up one byte does it now? Thanks :) – Drewdavid Nov 07 '13 at 23:11
@Drewdavid . . . I added the length in. Although MySQL accepts `bit` without the length, the documentation suggests that the length is required. – Gordon Linoff Nov 07 '13 at 23:14
1

`tinyint(1)` does **not** limit the values of that column to one digit. – Nov 07 '13 at 23:20

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

1 Answers1