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

sql sqlite tinyint
mk12 picture mk12 · Jun 15, 2012 · Viewed 16.4k times · Source

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.

Answer

user166390 picture user166390 · Jun 15, 2012

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 :-)

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More