Managing Integer Values in MySQL

When creating tables within MySQL databases we have to consider the data type that we use for each column. In this section we will look at the use of Integer values and the use of signed and unsigned values. For the purpose of this exercise we will be looking at creating a simple two column table, we are more concerned for the exercise the values that can be used for a specific Integer data-type rather than any longevity of the data being stored.

Creating a a table

To create the sample table it is a simple matter of defining the table and the columns that we wish. If we are in the same database that we want the tale created in then we can refer to the table directly using the table name only, if we are not in the database that we wish the tab le to be in we refer to the database.tablename.

To enter a database named tup and make that database out current context we can use the SQL command:

USE tup;

We could then create the users table within the tup database using code that starts:

CREATE TABLE users ……

If our current database context was something other than the database tup we could use code starting as this:

CREATE TABLE tup.users ……

Where possible if we keep object names to use standard characters and do not use system names in the object names then we will not need to use quoted identifiers. Quoted identifiers are just the posh names for object names , identifiers that are within quotes. The quotes we use are back-ticks and we use these object names or identifiers match system names or hold special characters. When using databasename.tablename format the identifiers are quoted and not the dot:

CREATE TABLE `tup`.`users`

Note that the quotes used are the back-tick and not a single quote.

In continuing with the table creation we need to open are parentheses to define the columns and their options , each column is separated from the next with a comma.

CREATE TABLE users (
userid TINYINT UNSIGNED NOT NULL PRIMARY KEY,
username varchar(50));

The two columns that we are defining have the column names of:

• userid
• username

If we look a little closer we can see that the userid column has been defined as:

• TINYINT UNSIGNED : This sets the data-type to be an unsigned tiny integer. Unsigned means that we can only have positive data values as we don’t allow for storage of the signing +/1 character. A tiny integer using 1 byte (8 bits) of storage and can be any whole number from:
  • -128 to +127 if it is signed
  • 0 t0 255 if it is unsigned
• NOT NULL : Here we set the null-ability of the column and we disallow NULL or empty values
• PRIMARY KEY : We add a primary key constraint the the userid column meaning the each values entered into this column in different rows or entries must contain unique values. This enforces the uniqueness of the userid value stored in the users table. Each user can be identified by this the userid.

To add to our row data we also have a column for the username, this can be character data up to 50 characters in length, varchar(50); as we do not mention null-ability it will default to NULL , allowing empty values for usernames.

Viewing the Table Schema

To view the table schema we can simply use the SQL command :

DESCRIBE users;

We can see the out put from the screen capture below:

For a complete listing of the table schema and options that were used including the inherited defaults we can use the code:

SHOW CREATE TABLE users;

The output is much more verbose than the DESCRIBE command as seen in the following screen capture:

Viewing the data-type for the userid column we notice that is shows as tinyint(3). The numerical value that follows the word tinyint relates to the maximum column width that is required, 3 characters to allow for the value of 255. If it was signed then the data-type would be tinyint(4) to allow for a 4 character width column for the signed minimum value of -128.

Inserting Data into Integer Fields

If data is too big to fit into the field then the system will adjust to the largest value possible. If we try to add the value of 128 into and unsigned tinyint then the value of 127 will be used. If 127 is in use and the is a UNIQUE or PRIMARY KEY constraint then the insert will fail. In the following screen shot we see that 128 is the insert value, but looking at the value shown from the SELECT statement we see that 127 was used as 126 is too big.

In the same way if we insert integers that are negatively signed and too big then the largest negative integer is used as we can see when we try to insert -129 into the same field.

Integer Data Types

You will be glad to know that we have more sizes available us for integers than just tinyint, even unsigned we have seen that this allows for just 255 as a maximum value.

A tinyint uses 1 byte of storage space, that is 8 bits. We can calculate the numbers that become available by using the output of 2 ^ 8, ( 2 to the power of 8). This results in the value of 256, 0 – 255, or with signed data -128 to + 127. SQL also has a function called POW or POWER so we can make the calculations in SQL should we wish:

SELECT POW(2,8)

We can see this illustrated in the following screen shot:

Integers in MySQL can be chosen from the following table ranging from TINYINT through to BIGINT

Type	Storage	Minimum Value	Maximum Value
	(Bytes)	(Signed/Unsigned)	Signed/Unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615