17

When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:

mysql> create table boolean_test (var1 boolean, var2 tinyint);    
Query OK, 0 rows affected (0.10 sec)   

mysql> show create table boolean_test;   
+--------------+-------------------------   
| Table        | Create Table
+--------------+-------------------------  
| boolean_test | CREATE TABLE `boolean_test` (  
  `var1` tinyint(1) DEFAULT NULL,  
  `var2` tinyint(4) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |   
+--------------+---------------------------  
1 row in set (0.00 sec)  

First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.

mysql> insert into boolean_test values(101,112);  
Query OK, 1 row affected (0.03 sec)   

mysql> select * from boolean_test;  
+------+------+  
| var1 | var2 |  
+------+------+  
|   10 |  112 |  
|  101 |  112 |  
+------+------+  
2 rows in set (0.00 sec)  

I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1).
Shouldn't I see a 1 for var1? I.e. just 1 display digits?

  • Why do you have two rows after one insert? I cannot fully comprehend this code. – WoodrowShigeru Jul 2 at 21:59
24

Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint.

You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For example, the Connector/J (Java connector) treats tinyint(1) as a boolean value, and instead of returning a numerical result to the application, it converts values to true and false. this can be changed via the tinyInt1isBit=false connection parameter.

13

A tinyint(1) can hold numbers in the range -128 to 127, due to the datatype being 8 bits (1 byte) - obviously an unsigned tinyint can hold values 0-255.

It will silently truncate out of range values:

mysql> create table a
    -> (
    ->    ttt tinyint(1)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values ( 127 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ( -128 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ( 128 );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into a values ( -129 );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from a;
+------+
| ttt  |
+------+
|  127 |
| -128 |
|  127 |
| -128 |
+------+
4 rows in set (0.00 sec)

mysql>

... unless you change the sql_mode or change the server config:

mysql> set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values ( -129 );
ERROR 1264 (22003): Out of range value for column 'ttt' at row 1
mysql>

The value used in the DDL for the datatype (eg: tinyint(1)) is, as you suspected, the display width. However, it is optional and clients don't have to use it. The standard MySQL client doesn't use it, for example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.