SIGNED, UNSIGNED and ZEROFILL For example, a TINYINT UNSIGNED can range from 0 to 255. Floating point and fixed-point types also can be UNSIGNED , but this only prevents negative values from being stored and doesn't alter the range.
The syntax of TINYINT data type is TINYINT(M), where M indicates the maximum display width (used only if your MySQL client supports it). Numeric Type Attributes.
If an integer value is too large to be represented as a TINYINT , use a SMALLINT instead. NULL considerations: Casting any non-numeric value to this type produces a NULL value. Examples: CREATE TABLE t1 (x TINYINT); SELECT CAST(100 AS TINYINT);
TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
UNSIGNED
is an attribute which can be added to many types. From the documentation:
data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type
You mark the unsigned with the keyword unsigned
. So, when making a table for an example:
CREATE TABLE `example_table` (
`example_col` tinyint(3) unsigned NOT NULL
);
See Create Table instead for more, or the Alter table.
Firstly, you should do something with negative values, otherwise ALTER TABLE...UNSIGNED
will throw an error. For example you could increase all values -
UPDATE table SET tiny_field = tiny_field + 128;
Then use ALTER TABLE to change field type/option.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With