MySQL – TINYINT
”;
The MySQL TINYINT Data Type
The MySQL TINYINT data type is used to store integer values within a very small range. It occupies just 1 byte (8 bits) of storage and can hold values from -128 to 127 for signed TINYINT or 0 to 255 for unsigned TINYINT.
When you define a TINYINT column in MySQL, by default it is considered as SIGNED. This means it can hold both positive and negative numbers within a specific range. Additionally, you can use either “TINYINT” or “INT1” to define such a column because they work the same way.
Syntax
Following is the syntax of the MySQL TINYINT data type −
TINYINT(M) [SIGNED | UNSIGNED | ZEROFILL]
Example
First, let us create a table with the name tinyint_table using the below query −
CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL );
Following is the output obtained −
Query OK, 0 rows affected, 1 warning (0.03 sec)
Now, let us try to insert some values (128, 128, 128) into these columns as shown below −
INSERT INTO tinyint_table VALUES (128, 128, 128);
An error is generated for the value in col1 because the value we inserted is out of range −
ERROR 1264 (22003): Out of range value for column ''col1'' at row 1
Next, if we try to insert a negative value into the TINYINT UNSIGNED column (“col2”), it will result in an error because UNSIGNED values cannot be negative −
INSERT INTO tinyint_table VALUES (127, -120, 128);
The error message displayed is as follows −
ERROR 1264 (22003): Out of range value for column ''col2'' at row 1
Similarly, if we insert -128 into the TINYINT ZEROFILL column (“col3”), an error will be generated −
INSERT INTO tinyint_table VALUES (127, 128, -128);
The output is as shown below −
ERROR 1264 (22003): Out of range value for column ''col3'' at row 1
However, if we insert values within the valid range, the insertion will succeed as shown below −
INSERT INTO tinyint_table VALUES (127, 128, 128);
Following is the output of the above code −
Query OK, 1 row affected (0.01 sec)
Finally, we can retrieve all the records present in the table using the following SELECT query −
SELECT * FROM tinyint_table;
This query will display the following result −
| col1 | col2 | col3 |
|---|---|---|
| 127 | 128 | 128 |
TINYINT Datatype Using a Client Program
We can also create column of the TINYINT datatype using the client program.
Syntax
To create a column of TINYINT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −
$sql = ''CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )''; $mysqli->query($sql);
To create a column of TINYINT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )"; con.query(sql);
To create a column of TINYINT datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )"; statement.execute(sql);
To create a column of TINYINT datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −
sql = ''CREATE TABLE tinyint_table (col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL)'' cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
//printf(''Connected successfully.
'');
$sql = ''CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )'';
$result = $mysqli->query($sql);
if ($result) {
printf("Table created successfully...!n");
}
// insert data into created table
$q = " INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)";
if ($res = $mysqli->query($q)) {
printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM tinyint_table";
if ($r = $mysqli->query($s)) {
printf("Table Records: n");
while ($row = $r->fetch_assoc()) {
printf(" Col_1: %s, Col_2: %s, Col_3: %s", $row["col1"], $row["col2"], $row["col3"]);
printf("n");
}
} else {
printf(''Failed'');
}
$mysqli->close();
Output
The output obtained is as follows −
Table created successfully...! Data inserted successfully...! Table Records: Col_1: 100, Col_2: 105, Col_3: 110
var mysql = require("mysql2");
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
}); //Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
//create a tinyint_table table, that accepts one column of tinyint type.
sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
con.query(sql);
//insert data into created table
sql =
"INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)";
con.query(sql);
//select datatypes of salary
sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''tinyint_table'' AND COLUMN_NAME = ''col2''`;
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
Output
The output produced is as follows −
[ { DATA_TYPE: ''tinyint'' } ]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TinyInt {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//TinyInt data types...!;
String sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
statement.execute(sql);
System.out.println("column of a TINYINT type created successfully...!");
ResultSet resultSet = statement.executeQuery("DESCRIBE tinyint_table");
while (resultSet.next()){
System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
}
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
Output
The output obtained is as shown below −
Connected successfully...! column of a TINYINT type created successfully...! col1 tinyint col2 tinyint unsigned col3 tinyint(3) unsigned zerofill
import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
host=''localhost'',
user=''root'',
password=''password'',
database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with Tinyint column
sql = ''''''
CREATE TABLE tinyint_table (
col1 TINYINT,
col2 TINYINT UNSIGNED,
col3 TINYINT ZEROFILL
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = "INSERT INTO tinyint_table (col1, col2, col3) VALUES (127, 128, 128);"
cursorObj.execute(insert_query)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM tinyint_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
print(row)
cursorObj.close()
connection.close()
Output
Following is the output of the above code −
The table is created successfully! Rows inserted successfully. Table Data: (127, 128, 128)
”;