[SOLVED] How to determine if a column is unsigned?

How to determine if a column is unsigned?

I'm currently trying to list all columns of a specific table and determine if each column is unsigned or not.

Here, an example of my test fixture:

CREATE TABLE ttypes
(
    cbiginteger BIGINT UNSIGNED,
    cinteger INT UNSIGNED,
    csmallinteger SMALLINT UNSIGNED
) ENGINE = InnoDB;

In order to list all columns of a specific table, I have found two possibilities:

SHOW FULL COLUMNS
FROM ttypes;

According to the documentation, this query returns these fields: Field, Type, Null, Default, Extra & Comment. None of them allows me to determine if a column is unsigned or not.

After that, I look at the information_schema.columns which is the base table used by the SHOW COLUMNS query.

SELECT ...
FROM information_schema.columns
WHERE table_name = 'ttypes';

Unfortunately, none of the result fields allows me to determine if a column is unsigned or not.

Solution

As far as I can tell, the only place those attributes are stored is in COLUMN_TYPE in INFORMATION_SCHEMA.COLUMNS.

That should be included in the output from SHOW COLUMNS (within Type):

mysql> show columns from ttypes;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| cbiginteger   | bigint(20) unsigned  | YES  |     | NULL    |       |
| cinteger      | int(10) unsigned     | YES  |     | NULL    |       |
| csmallinteger | smallint(5) unsigned | YES  |     | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Unfortunately you'd have to parse out the contents of Type and find unsigned, or not unsigned in there - it doesn't put anything in for signed columns.