CODEWITHSUNDEEP
mysqldatabaseboolean
pritam parab
pritam parab

Reputation: 85

MySql doesn't accept boolean as its column datatype

I have a Java program which stores data in MySQL database in two states. For this purpose I wanted to use BOOLEAN. But whenever I enter BOOLEAN it's getting changed into TINYINT.

Is there any other way to store data in two states?

Upvotes: 3

Views: 7160

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562368

MySQL doesn't have a native boolean type.

https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html says:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

This means the "boolean" type is still an 8-bit signed integer, and the (1) syntax is not a size limit. It doesn't prevent the column from storing integer values from -128 to 127. It's up to you to refrain from storing those values.

MySQL also supports a BIT data type: https://dev.mysql.com/doc/refman/5.7/en/bit-type.html

The MySQL JDBC driver translates BIT(1) into java.lang.Boolean. See https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html for JDBC data type mappings.

But the BIT data type has had some bugs in its history. I avoid using it.

Bit probably won't save any space, anyway, if that's what you are hoping for. If you had a bunch of BIT columns defined consecutively in your table, they would be stored compactly, up to 8 columns per byte. But the minimum storage is still 1 byte, so if you had 1 BIT column, it would still take a whole byte.

Re your questions:

It doesn't take much code to test this out.

I created a test table and put one row of values in it:

CREATE TABLE `foo` (
  `b` bool DEFAULT '1', /* this is a synonym for TINYINT(1) */
  `ti` tinyint DEFAULT '1',
  `tiu` tinyint unsigned DEFAULT '1',
  `si` smallintDEFAULT '1',
  `siu` smallint unsigned DEFAULT '1',
  `i` int DEFAULT '1',
  `iu` int unsigned DEFAULT '1',
  `bi` bigint DEFAULT '1',
  `biu` bigint unsigned DEFAULT '1'
);

INSERT INTO foo () VALUES ();

Then I called some JDBC code and used getObject() for each column, and asked it to tell me what data type it returned:

    stmt = conn.createStatement(); 
    rs = stmt.executeQuery("SELECT * FROM foo");
    while (rs.next()) {

        Object b = rs.getObject("b");
        System.out.println("b ("+b.getClass().getSimpleName()+"):\t" + b);

        Object ti = rs.getObject("ti");
        System.out.println("ti ("+ti.getClass().getSimpleName()+"):\t" + ti);

        Object tiu = rs.getObject("tiu");
        System.out.println("tiu ("+tiu.getClass().getSimpleName()+"):\t" + tiu);

        Object si = rs.getObject("si");
        System.out.println("si ("+si.getClass().getSimpleName()+"):\t" + si);

        Object siu = rs.getObject("siu");
        System.out.println("siu ("+siu.getClass().getSimpleName()+"):\t" + siu);

        Object i = rs.getObject("i");
        System.out.println("i ("+i.getClass().getSimpleName()+"):\t" + i);

        Object iu = rs.getObject("iu");
        System.out.println("iu ("+iu.getClass().getSimpleName()+"):\t" + iu);

        Object bi = rs.getObject("bi");
        System.out.println("bi ("+bi.getClass().getSimpleName()+"):\t" + bi);

        Object biu = rs.getObject("biu");
        System.out.println("biu ("+biu.getClass().getSimpleName()+"):\t" + biu);
    }

Output:

b (Boolean):    true
ti (Integer):   1
ti2 (Integer):  1
tiu (Integer):  1
si (Integer):   1
siu (Integer):  1
i (Integer):    1
iu (Long):  1
bi (Long):  1
biu (BigInteger):   1

I'm testing with MySQL Connector/J 5.1.44.

So it seems that TINYINT(1) is handled specially by the JDBC driver. It automatically converts it to a java.lang.Boolean.

Again, TINYINT(1) has no real effect on the range of possible values in MySQL. It's an 8-bit signed integer type. But the JDBC driver has special code to look for the (1) length option and it uses that as an advisory to make it cast to a java.lang.Boolean.

java.lang.Integer is okay up to the unsigned 32-bit INT, then it has to use java.lang.Long to handle the unsigned INT. Then a BigInteger for an unsigned BIGINT. Java integer types are not unsigned, so to handle the larger values of an unsigned INT or BIGINT, Java has to use the larger Integer type.

Upvotes: 1

Daniele Cappuccio
Daniele Cappuccio

Reputation: 2202

Boolean and TINYINT(1) are synonyms in mysql, meaning that you can use them interchangeably without raising any problem.

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

MySQL uses TINYINT(1) to mimic the behaviour of Boolean type, so make sure you use TINYINT(1) as the data type of your column, not TINYINT.

Alternatively, you can use BOOL or BOOLEAN which are both synonyms for TINYINT(1).

Similarly, the values TRUE and FALSE are merely aliases for 1 and 0, respectively in MySQL.

Upvotes: 4

Related Questions