MySQL Data Types

A Data Type specifies a particular type of data, like integer, floating points, Boolean, etc. It
also identifies the possible values for that type, the operations that can be performed on that
type, and the way the values of that type are stored. In MySQL, each database table has many
columns and contains specific data types for each column.

We can determine the data type in MySQL with the following characteristics:

 The type of values (fixed or variable) it represents.

 The storage space it takes is based on whether the values are a fixed-length or variable
length.
 Its values can be indexed or not.
 How MySQL performs a comparison of values of a particular data type.

MySQL supports a lot number of SQL

standard data types in various categories. It uses many different data types that can be broken
into the following categories: numeric, date and time, string types, spatial types, and JSON
data types.

Numeric Data Type

MySQL has all essential SQL numeric data types. These data types can include the exact
numeric data types (For example, integer, decimal, numeric, etc.), as well as the approximate
numeric data types (For example, float, real, and double precision). It also supports BIT
datatype to store bit values. In MySQL, numeric data types are categories into two types,
either signed or unsigned except for bit data type.

The following table contains all numeric data types that support in MySQL

:
Data Type
Description
Syntax
It is a very small integer that can be signed or unsigned. If signed, the
allowable range is from -128 to 127. If unsigned, the allowable range is
TINYINT
from 0 to 255. We can specify a width of up to 4 digits. It takes 1 byte for
storage.
It is a small integer that can be signed or unsigned. If signed, the allowable
range is from -32768 to 32767. If unsigned, the allowable range is from 0
SMALLINT
to 65535. We can specify a width of up to 5 digits. It requires 2 bytes for
storage.
It is a medium-sized integer that can be signed or unsigned. If signed, the
allowable range is from -8388608 to 8388607. If unsigned, the allowable
MEDIUMINT
range is from 0 to 16777215. We can specify a width of up to 9 digits. It
requires 3 bytes for storage.
INT It is a normal-sized integer that can be signed or unsigned. If signed, the
allowable range is from -2147483648 to 2147483647. If unsigned, the
 allowable range is from 0 to 4294967295. We can specify a width of up to
11 digits. It requires 4 bytes for storage.
It is a large integer that can be signed or unsigned. If signed, the allowable
range is from -9223372036854775808 to 9223372036854775807. If
BIGINT
unsigned, the allowable range is from 0 to 18446744073709551615. We
can specify a width of up to 20 digits. It requires 8 bytes for storage.
It is a floating-point number that cannot be unsigned. You can define the
display length (m) and the number of decimals (d). This is not required and
FLOAT(m,d) will default to 10,2, where 2 is the number of decimals, and 10 is the total
number of digits (including decimals). Decimal precision can go to 24
places for a float type. It requires 2 bytes for storage.
It is a double-precision floating-point number that cannot be unsigned.
You can define the display length (m) and the number of decimals (d).
DOUBLE(m,d) This is not required and will default to 16,4, where 4 is the number of
decimals. Decimal precision can go to 53 places for a double. Real is a
synonym for double. It requires 8 bytes for storage.
An unpacked floating-point number that cannot be unsigned. In unpacked
decimals, each decimal corresponds to one byte. Defining the display
DECIMAL(m,d)
length (m) and the number of decimals (d) is required. Numeric is a
synonym for decimal.
It is used for storing bit values into the table column. Here, M determines
BIT(m)
the number of bit per value that has a range of 1 to 64.
It is used only for the true and false condition. It considered numeric value
BOOL
1 as true and 0 as false.
BOOLEAN It is Similar to the BOOL.

Date and Time Data Type:

This data type is used to represent temporal values such as date, time, datetime, timestamp,
and year. Each temporal type contains values, including zero. When we insert the invalid
value, MySQL cannot represent it, and then zero value is used.

The following table illustrates all date and time data types that support in MySQL:

Data Type
Maximum Size Explanation
Syntax
The default is 4 digits. It takes 1 byte for
YEAR[(2|4)] Year value as 2 digits or 4 digits.
storage.
Values range from '1000-01-01' Displayed as 'yyyy-mm-dd'. It takes 3
DATE
to '9999-12-31'. bytes for storage.
Values range from '-838:59:59' Displayed as 'HH:MM:SS'. It takes 3
TIME
to '838:59:59'. bytes plus fractional seconds for storage.
Values range from '1000-01-01 Displayed as 'yyyy-mm-dd hh:mm:ss'. It
DATETIME 00:00:00' to '9999-12-31 takes 5 bytes plus fractional seconds for
23:59:59'. storage.
Values range from '1970-01-01 Displayed as 'YYYY-MM-DD
TIMESTAMP(m) 00:00:01' UTC to '2038-01-19 HH:MM:SS'. It takes 4 bytes plus
03:14:07' TC. fractional seconds for storage.
String Data Types:

The string data type is used to hold plain text and binary data, for example, files, images, etc.
MySQL can perform searching and comparison of string value based on the pattern matching
such as LIKE operator, Regular Expressions, etc.

The following table illustrates all string data types that support in MySQL:

Data Type Syntax Maximum Size Explanation

Here size is the number of characters
It can have a maximum size of to store. Fixed-length strings. Space
CHAR(size)
255 characters. padded on the right to equal size
characters.
It can have a maximum size of Here size is the number of characters
VARCHAR(size)
255 characters. to store. Variable-length string.
It can have a maximum size of Here size is the number of characters
TINYTEXT(size)
255 characters. to store.
Maximum size of 65,535 Here size is the number of characters
TEXT(size)
characters. to store.
It can have a maximum size of Here size is the number of characters
MEDIUMTEXT(size)
16,777,215 characters. to store.
It can have a maximum size of
Here size is the number of characters
LONGTEXT(size) 4GB or 4,294,967,295
to store.
characters.
Here size is the number of binary
characters to store. Fixed-length
It can have a maximum size of
BINARY(size) strings. Space padded on the right to
255 characters.
equal size characters.
(introduced in MySQL 4.1.2)
Here size is the number of characters
It can have a maximum size of
VARBINARY(size) to store. Variable-length string.
255 characters.
(introduced in MySQL 4.1.2)
It is short for enumeration, which
It takes 1 or 2 bytes that depend
means that each column may have
on the number of enumeration
ENUM one of the specified possible values.
values. An ENUM can have a
It uses numeric indexes (1, 2, 3…) to
maximum of 65,535 values.
represent string values.
It takes 1, 2, 3, 4, or 8 bytes It can hold zero or more, or any
that depends on the number of number of string values. They must
SET
set members. It can store a be chosen from a predefined list of
maximum of 64 members. values specified during table creation.

Binary Large Object Data Types (BLOB):

BLOB in MySQL is a data type that can hold a variable amount of data. They are categories
into four different types based on the maximum length of values can hold.

The following table shows all Binary Large Object data types that support in MySQL:
Data Type Syntax Maximum Size
TINYBLOB It can hold a maximum size of 255 bytes.
BLOB(size) It can hold the maximum size of 65,535 bytes.
MEDIUMBLOB It can hold the maximum size of 16,777,215 bytes.
LONGBLOB It can hold the maximum size of 4gb or 4,294,967,295 bytes.

Spatial Data Types

It is a special kind of data type which is used to hold various geometrical and geographical
values. It corresponds to OpenGIS classes. The following table shows all spatial types that
support in MySQL:

Data Types Descriptions

It is a point or aggregate of points that can hold spatial values
GEOMETRY
of any type that has a location.
A point in geometry represents a single location. It stores the
POINT
values of X, Y coordinates.
It is a planar surface that represents multisided geometry. It
POLYGON can be defined by zero or more interior boundary and only one
exterior boundary.
It is a curve that has one or more point values. If it contains
LINESTRING
only two points, it always represents Line.
It is a kind of geometry that has a collection of zero or more
GEOMETRYCOLLECTION
geometry values.
It is a multi-curve geometry that has a collection of linestring
MULTILINESTRING
values.
It is a collection of multiple point elements. Here, the point
MULTIPOINT
cannot be connected or ordered in any way.
It is a multisurface object that represents a collection of
MULTIPLYGON multiple polygon elements. It is a type of two-dimensional
geometry.

JSON Data Type

MySQL provides support for native JSON data type from the version v5.7.8. This data type
allows us to store and access the JSON document quickly and efficiently.

The JSON data type has the following advantages over storing JSON-format strings in a
string column:

1. It provides automatic validation of JSON documents. If we stored invalid documents

in JSON columns, it would produce an error.
2. It provides an optimal storage format.

MySQL Variables
Variables are used for storing data or information during the execution of a program. It is a
way of labeling data with an appropriate name that helps to understand the program more
clearly by the reader. The main purpose of the variable is to store data in memory and can be
used throughout the program.

MySQL can use variables in three different ways, which are given below:

1. User-Defined Variable
2. Local Variable
3. System Variable

User-Defined Variable

Sometimes, we want to pass values from one statement to another statement. The user-
defined variable enables us to store a value in one statement and later can refer it to another
statement. MySQL provides a SET and SELECT statement to declare and initialize a
variable. The user-defined variable name starts with @ symbol.

The user-defined variables are not case-sensitive such as @name and @NAME; both are the
same. A user-defined variable declares by one person cannot visible to another person. We
can assign the user-defined variable into limited data types like integer, float, decimal, string,
or NULL. The user-defined variable can be a maximum of 64 characters in length.

Syntax

The following syntax is used to declare a user-defined variable.

1. By using the SET statement

1. SET @var_name = value;

NOTE: We can use either '=' or ':=' assignment operator with the SET statement.

2. By using the SELECT statement

1. SELECT @var_name := value;

Example1

Here, we are going to assign a value to a variable by using the SET statement.

1. mysql> SET @name='peter';

Then, we can display the above value by using the SELECT statement.

1. mysql> SELECT @name;

Output
Example 2

Let us create table students in the MySQL database, as shown below:

Run the following statement to get the maximum age of the student in the 'students' table and
assign the age to the user-defined variable @maxage.

1. mysql> SELECT @maxage:= MAX(age) FROM students;

It will give the following output.

Now, run the SELECT statement that uses the @maxage variable to return the maximum age
of the student.

1. mysql> SELECT firstname, lastname, age FROM students WHERE age = @maxage;

After successful execution of the above statement, we will get the following result:

Example3

If we access the undeclared variable, it will give the NULL output.

1. Mysql> SELECT @var1;

Output
Local Variable

It is a type of variable that is not prefixed by @ symbol. The local variable is a strongly typed
variable. The scope of the local variable is in a stored program block in which it is declared.
MySQL uses the DECLARE keyword to specify the local variable. The DECLARE
statement also combines a DEFAULT clause to provide a default value to a variable. If you
do not provide the DEFAULT clause, it will give the initial value NULL. It is mainly used in
the stored procedure program.

Syntax

We can use the DECLARE statement with the following syntax:

1. DECLARE variable_name datatype(size) [DEFAULT default_value];

Let us see the following example to use the local variable.

Example

1. mysql> DECLARE total_price Oct(8,2) DEFAULT 0.0;

We can also define two or more variables with the same data type by using a single
DECLARE statement.

1. mysql> DECLARE a,b,c INT DEFAULT 0;

The below example explains how we can use the DECLARE statement in a stored procedure.

1. DELIMITER //
2. Create Procedure Test()
3. BEGIN
4. DECLARE A INT DEFAULT 100;
5. DECLARE B INT;
6. DECLARE C INT;
7. DECLARE D INT;
8. SET B = 90;
9. SET C = 45;
10. SET D = A + B - C;
11. SELECT A, B, C, D;
12. END //
13. DELIMITER ;
After successful execution of the above function, call the stored procedure function as below:

1. mysql> CALL Test();

It will give the following output:

System Variable

System variables are a special class to all program units, which contains predefined
variables. MySQL contains various system variables that configure its operation, and each
system variable contains a default value. We can change some system variables dynamically
by using the SET statement at runtime. It enables us to modify the server operation without
stop and restart it. The system variable can also be used in the expressions.

MySQL server gives a bunch of system variables such as GLOBAL, SESSION, or MIX
types. We can see the GLOBAL variable throughout the lifecycle of the server, whereas the
SESSION variable remains active for a particular session only.

We can see the names and values of the system variable by using the following ways:

1. To see the current values used by the running server, execute the following command.

1. mysql> SHOW VARIABLES;

2.
3. OR,
4.
5. Mysql> SELECT @@var_name;

2. When we want to see the values based on its compiled-in defaults, use the following
command.

1. mysql> mysqld --verbose --help

Example1

1. mysql> SHOW VARIABLES LIKE '%wait_timeout%';

Output
Example 2

1. mysql> SELECT @@key_buffer_size;

Output

How to install MySQL

MySQL is one of the most popular relational database management software that is widely
used in today's industry. It provides multi-user access support with various storage engines. It
is backed by Oracle Company. In this section, we are going to learn how we can download
and install MySQL for beginners.

Prerequisites

The following requirements should be available in your system to work with MySQL:

 MySQL Setup Software

 Microsoft .NET Framework 4.5.2
 Microsoft Visual C++ Redistributable for Visual Studio 2019
 RAM 4 GB (6 GB recommended)

Download MySQL

Follow these steps:

Step 1: Go to the official website of MySQL and download the community server edition
software. Here, you will see the option to choose the Operating System, such as Windows.
Step 2: Next, there are two options available to download the setup. Choose the version
number for the MySQL community server, which you want. If you have good internet
connectivity, then choose the mysql-installer-web-community. Otherwise, choose the other
one.

Installing MySQL on Windows

Step 1: After downloading the setup, unzip it anywhere and double click the MSI
installer .exe file. It will give the following screen:

Step 2: In the next wizard, choose the Setup Type. There are several types available, and
you need to choose the appropriate option to install MySQL product and features. Here, we
are going to select the Full option and click on the Next button.
This option will install the following things: MySQL Server, MySQL Shell, MySQL Router,
MySQL Workbench, MySQL Connectors, documentation, samples and examples, and many
more.

Step 3: Once we click on the Next button, it may give information about some features that
may fail to install on your system due to a lack of requirements. We can resolve them by
clicking on the Execute button that will install all requirements automatically or can skip
them. Now, click on the Next button.
Step 4: In the next wizard, we will see a dialog box that asks for our confirmation of a few
products not getting installed. Here, we have to click on the Yes button.

After clicking on the Yes button, we will see the list of the products which are going to be
installed. So, if we need all products, click on the Execute button.
Step 5: Once we click on the Execute button, it will download and install all the products.
After completing the installation, click on the Next button.
Step 6: In the next wizard, we need to configure the MySQL Server and Router. Here, I am
not going to configure the Router because there is no need to use it with MySQL. We are
going to show you how to configure the server only. Now, click on the Next button.

Step 7: As soon as you will click on the Next button, you can see the screen below. Here, we
have to configure the MySQL Server. Now, choose the Standalone MySQL Server/Classic
MySQL Replication option and click on Next. Here, you can also choose the InnoDB Cluster
based on your needs.
Step 8: In the next screen, the system will ask you to choose the Config Type and other
connectivity options. Here, we are going to select the Config Type as 'Development
Machine' and Connectivity as TCP/IP, and Port Number is 3306, then click on Next.
Step 9: Now, select the Authentication Method and click on Next. Here, I am going to select
the first option.
Step 10: The next screen will ask you to mention the MySQL Root Password. After filling
the password details, click on the Next button.
Step 11: The next screen will ask you to configure the Windows Service to start the server.
Keep the default setup and click on the Next button.
Step 12: In the next wizard, the system will ask you to apply the Server Configuration. If you
agree with this configuration, click on the Execute button.
Step 13: Once the configuration has completed, you will get the screen below. Now, click on
the Finish button to continue.
Step 14: In the next screen, you can see that the Product Configuration is completed. Keep
the default setting and click on the Next-> Finish button to complete the MySQL package
installation.
Step 15: In the next wizard, we can choose to configure the Router. So click on Next->Finish
and then click the Next button.
Step 16: In the next wizard, we will see the Connect to Server option. Here, we have to
mention the root password, which we had set in the previous steps.

In this screen, it is also required to check about the connection is successful or not by clicking
on the Check button. If the connection is successful, click on the Execute button. Now, the
configuration is complete, click on Next.

Step 17: In the next wizard, select the applied configurations and click on the Execute button.
Step 18: After completing the above step, we will get the following screen. Here, click on the
Finish button.
Step 19: Now, the MySQL installation is complete. Click on the Finish button.

Verify MySQL installation

Once MySQL has been successfully installed, the base tables have been initialized, and the
server has been started, you can verify its working via some simple tests.

Open your MySQL Command Line Client; it should have appeared with a mysql> prompt.
If you have set any password, write your password here. Now, you are connected to the
MySQL server, and you can execute all the SQL command at mysql> prompt as follows:

For example: Check the already created databases with show databases command:
What is the Primary key?

A primary key is a single field or combination of fields that contain a unique record. It must
be filled. None of the fields of the primary key can contain a null value. A table can have
only one primary key.

NOTE: In Oracle, the total number of columns cannot be more than 32.

MySQL Connection
A connection is a computer science facility that allows the user to connect with the database
server software. A user can connect with the database server, whether on the same
machine or remote locations. Therefore, if we want to work with the database server to send
commands and receive answers in the form of a result set, we need connections. In this
article, we are going to learn how we can connect to MySQL Server in various ways.

MySQL Connection Types

MySQL provides various ways to connect with the database server. Once we have installed
the MySQL server, we can connect it using any of the client programs that are listed
below:

1. Command-line client
2. MySQL Workbench
3. PHP Script.

MySQL Server Connection Using command-line client

MySQL command-line client program provides interaction with the database server in an
interactive and non-interactive mode. We can see this program in the bin directory of the
MySQL's installation folder. We can open the MySQL command prompt by navigating to
the bin directory of the MySQL's installation folder and type:

1. MySQL

If we find the MySQL program in the PATH, we can use the below command to connect to
the MySQL Server:

1. mysql -u root -p

In the syntax, the -u root indicates that we will connect to the MySQL server using the root
user account and -p instructs MySQL to ask for a password.

Next, we need to type the password for the root user account and press Enter. If everything is
correct, it should give the screen as follows:

This screen indicates that we have successfully connected with the MySQL database server,
where we can send commands and receive answers in the form of a result set.

Suppose we want to display all databases available in the current server; we can use the
command as follows:

1. mysql> SHOW DATABASES;

It will give the below output:

If you want to disconnect the opened MySQL database server, you need to use the exit
command.

1. mysql> EXIT;

Connect to Database Server Using MySQL Workbench

We can connect to the MySQL database server in workbench by using the following steps:

Step 1: Launch the MySQL Workbench. We should get the following screen:
Step 2: Navigate to the menu bar, click on the 'Database' and choose Connect to Database
option or press the CTRL+U command. We can also connect with the database server by just
clicking the plus (+) button located next to the MySQL Connections. See the below image:

Step 3: After choosing any of the options, we will get the below screen:
Step 4: Fill the box to create a connection, such as connection name and username,
whatever you want. By default, the username is the root, but we can also change it with a
different username in the Username textbox. After filling all boxes, click the Store in
Vault ... button to write the password for the given user account.

Step 5: We will get a new window to write the password and click the OK button.
Step 6: After entering all the details, click on the Test Connection to test the database
connectivity is successful or not. If the connection is successful, click on the OK button.

Step 7: Again, click on the OK button for saving connection setup. After finishing all the
setup, we can see this connection under MySQL Connections for connecting to the MySQL
database server. See the below output where we have Localhost3 connection name:
Step 8: Now, we can click this newly created connection that displays the current schemas
and a pane for entering queries:

Connect to MySQL Server Using PHP Script

The simplest way to connect with the MySQL database server using the PHP script is to use
the mysql_connect() function. This function needs five parameters and returns the MySQL
link identifier when the connection becomes successful. If the connection is failed, it returns
FALSE.

Syntax

The following is the syntax for MySQL connection using PHP Script:

1. connection mysql_connect(server, user, passwordd, new_link, client_flag);

Let us explain the mysql_connect() function parameters:

Server: It is the name of a host that runs the database server. By default, its value will be
lcalhost:3306.

User: It is the name of a user who accesses the database. If we will not specify this field, it
assumes the default value that will be the name of a user that owns the server process.

Password: It is the password of a user whose database you are going to access. If we will not
specify this field, it assumes the default value that will be an empty password.

New_link: If we make a second call with the same arguments in the mysql_connect()
function, MySQL does not establish a new connection. Instead, we will get the identifier of
the already opened database connection.

Client_flags: This parameter contains a combination of the below constants:

 MYSQL_CLIENT_SSL: It uses SSL encryption.

 MYSQL_CLIENT_COMPRESS: It uses a compression protocol.
 MYSQL_CLIENT_IGNORE_SPACE: It provides space after function names.
 MYSQL_CLIENT_INTERACTIVE: It provides a timeout before closing the connection.

If we want to disconnect from the MySQL database server, we can use another PHP
function named mysql_close(). It accepts only a single parameter that will be a connection
returned by the mysql_connect() function. Its syntax is given below:

1. bool mysql_close ( resource $link_identifier );

If we do not specify any resource, MySQL will close the last opened database. This function
returns true when the connection is closed successfully. Otherwise returns a FALSE value.

Example

The following example explain how to connect to a MySQL server using PHP Script:

1. <html>
2. <head>
3. <title>MySQL Server Connection</title>
4. </head>
5. <body>
6. <?php
7. $servername = 'localhost:3306';
8. $username = 'javatpoint';
9. $dbpass = 'jtp123';
10. $conn = mysql_connect($servername, $username, $password);
11. if(! $conn ) {
12. die('Connection failed: ' . mysql_error());
13. }
14. echo 'Connection is successful';
15. mysql_close($conn);
16. ?>
17. </body>
18. </html>