What does tinyint(3) mean in (SQLite) SQL?

Question

I realize that tinyint is a single byte integer (by the way, is it signed or unsigned?). What does the argument (3) signify? I've searched and couldn't find the answer.

Friedrich

I'm using sqlite and I don't see any difference between tinyint and tinyint(3). – Friedrich

@Mk12 It is because SQLite "mock implements" data-types (basically, it accepts the syntax but otherwise ignores it). Read the fine manual. – Powerboat

Answer 1

See this blog page about MySQL column type definitions.

For numeric types length modifier is actually display width, which has nothing to do with what can be stored in field. Yes, that’s it – both TINYINT(1) and TINYINT(4) can store values in range -128..127 (or for unsigned values 0..255), and are absolutely identical datatypes, with one small twist (which concerns date retrieval – see below). Here is explanation of what is meant by display width, taken directly from the source:

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

Murage

The question was about SQLite, but I realize that the OP added this precision after you answered. The other answer is more relevant now, not sure why this one is accepted. – Pork

Answer 2

SQLite "mock implements" data-types on columns. Basically, it accepts the standard SQL syntax but otherwise ignores it. (But see type affinity and coercions.)

See Manifest Typing in the "differences" documentation.

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

Also see Datatypes in SQLite Version 3 and Type Affinity:

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

• If the declared type contains the string "INT" then it is assigned INTEGER affinity.

• [other rules omitted for brevity]

---

For other databases, see the appropriate database-specific documentation :-)

Powerboat