How do I add a boolean field in MySQL?

In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1. This will provide the boolean nature for a data type. 

Regarding the storage, if there are less than 9 columns of the bit data are present in the table,  they are stored as 1 byte. And for 9 to 16 BIT columns,  their consumption is 2 bytes and so on it will hold. In this article, let us how we can add a Boolean i.e. BIT datatype to an existing table in SQL SERVER.

Let us assume there is a database “GEEKSFORGEEKS” is available and there is a table called “Authors” available with the following data.

Query:

USE GEEKSFORGEEKS
SELECT * FROM Authors;

Output:

Let us add a BIT data type to the “Authors” table. Bit datatype is helpful to represent Boolean nature either True(1) or False(0) and they are the only allowed values for BIT datatype.

-- Add a column named "isActiveAuthor" 
with BIT datatype for "Authors" table
ALTER TABLE Authors  ADD isActiveAuthor BIT;

As there are already few rows available in the table, we can add a new column “isActiveAuthor” as a NULL pattern only. After that, we can update the data. After adding the column, 

Query:

SELECT * FROM Authors;

Output:

So, when a BIT datatype column is added to an existing table, its value will be populated with “NULL” values.

Now let us try to update the column with a condition that if “NumberOfPosts” > 5, set “isActiveAuthor” to have the value 1 or else 0. 

-- Let us assume that if "NumberOfPosts" 
   is more than 5, author is active enough to write articles
-- Hence for this condition , set "isActiveAuthor"
   column to have value 1
UPDATE Authors SET isActiveAuthor = 1 WHERE NumberOfPosts > 5;

-- On the other hand, if "NumberOfPosts"
   is less  than and equal to 5, 
-- author is not active in the recent days
   and hence set "isActiveAuthor" column to have value 0
UPDATE Authors SET isActiveAuthor = 0 WHERE NumberOfPosts <= 5;

SELECT * FROM Authors;

Output:

As the BIT column supports only 0 or 1 as values, we have populated like above.

Let us try to update the value other than 0 or 1 

-- Trying to update the "isActiveAuthor"
   with value 2, though errors are not 
   produced, output is different
UPDATE Authors SET isActiveAuthor = 
2 WHERE NumberOfPosts = 5;

SELECT * FROM Authors;

Output:

Reason for the updated value of 1 in the 3rd row :

Though an update is given to get the value of 2, because of the “BIT” datatype for “isActiveAuthor” column, its value is converted to 1 only as the “BIT” datatype supports either 0 or 1 only. i.e. values other than 0 are converted to 1 and updated in the table.

We can check the same here with different examples

DECLARE @isValid BIT

-- Initially it will have "NULL" value 
SELECT @isValid AS BitType
 
--Assigning any nonzero value converts it to 1
-- i.e. other than 0, if any
   value is provided , it is converted to 1
SET @isValid=9
 
SELECT @isValid AS BitType
 
--Assigning any nonzero value converts it to 1
SET @isValid=-100
 
SELECT @isValid AS BitType

Output:

This proves the “BIT” datatype accepts only the “Boolean” value i.e. either 0 or 1 only. If a nonzero value is given, it is converted to 1 only.

In the article, we are going to discuss data types including string, numeric, date and time, spatial, and JSON supported by MySQL. Also, we’ll provide examples of their usage and see how to change a data type for the table column using dbForge Studio for MySQL.

Contents

What is a Data Type

Selecting an appropriate data type can be very important. When creating a table, you need to specify a table name and a data type that not only defines what data can be stored in the table column but also what influence it may have on database performance. A data type also indicates a data range to be stored in each column of the table. When specifying data types, you can set a field size.

In MySQL, there are various data types that are grouped in numeric (integer, float, boolean, etc.), date and time (DATETIME, DATE, etc.), string (CHAR, VARCHAR, etc.), spatial, and JSON. For example, if the column data type is numeric, it means that only numerical data can be stored in the column and you can define its maximum length in brackets.

The syntax of data types is as follows:

CREATE TABLE table_name (
      column1_name data type(length),
      column2_name data type(length),
);

Here is the example of the CREATE statement displaying MySQL data types:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_item VARCHAR(255) NOT NULL,
    use_by date,
    price int,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Types in MySQL

MySQL supports several standard SQL data types. Each column can contain only one data type. In MySQL, data types are grouped in different categories:

• Numeric
• Date and time
• String
• Spatial
• JSON.

MySQL String Data Types (Text Formats)

In MySQL, string data types usually store data as long strings of text, for example, feedback or product description. Strings can consist of letters, numbers, or binary media files such as images, photos, or audio files.

The MySQL string data types are divided into:

• TEXT
• BLOB
• CHAR and VARCHAR
• BINARY and VARBINARY
• ENUM
• SET

TEXT Data Type

The MySQL TEXT data type is used to store long-text strings to display information about the table object, such as product descriptions, blog comments, etc. The storage size of the TEXT data type varies from 1 byte to 4 GB. Unlike the numeric data types, you do not have to set a length next to the TEXT data type in the table column. Moreover, TEXT values are not stored in the server’s memory but use the disk instead. Therefore, the TEXT data types require +1 additional byte to retrieve data.

In MySQL, there are four different TEXT data types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

The table displays the allowable storage in bytes for the TEXT data types and cases when they can be used.

TypeStorageMaximum number of charactersOverhead storage (in bytes)UsageTINYTEXT2552551To store short-text strings such as links, product description or summaryTEXT64 kB655352To store texts such as articles that do not exceed the specified length of charactersMEDIUMTEXT16 MB167772153To store large texts such as whitepapers or booksLONGTEXT4 GB42949672954To store huge texts such as computer programs or applications

BLOB Datatype in MySQL

Unlike the TEXT data types, which are non-binary string data types, the BLOB data types are binary strings. In MySQL, the BLOB data type represents a binary large object and can be used to store binary media data, such as audio or video links, images, or files.

The BLOB data types, including TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, have a variable length, i.e. additional bytes (from 1 to 4) are required to store a value length in the column. For example, the maximum size of data to be stored is as follows:

• TINYBLOB => 255 bytes + 1 byte
• BLOB => 65535 + 2 bytes
• MEDIUMBLOB => 16777215 + 3 bytes
• LONGBLOB => 4294967295 + 4 bytes

When comparing BLOB and TEXT data types, it should be noted that BLOB is defined as numeric values, while TEXT – as character strings having a character set. This should be taken into account when comparing and sorting information.

CHAR and VARCHAR data type

In MySQL, the CHAR data types store non-binary strings with a fixed length that reaches 255 characters, while the VARCHAR data types store non-binary strings with a variable length having a maximum size of up to 65535 characters.

For both data types, you need to set a size parameter in characters (in brackets) when creating a column. The size parameter represents the column length for a CHAR data type and the maximum column length for a VARCHAR data type. For example, CHAR(3) refers to up to 3 characters for the column value.

The main distinction between the CHAR and VARCHAR data types is a way of storing data. CHAR adds spaces to values on the right to the specified length, for example, CHAR(3) will be displayed as follows ‘table ‘. VARCHAR outputs the value as it is, without any additional spaces – VARCHAR(3) will be displayed as ‘table’.

It should be noted that when defining a datatype for a phone number in MySQL, VARCHAR is more preferable to integers as sometimes there may be special symbols or characters. In addition, VARCHAR simplifies validation.

BINARY and VARBINARY Types

Though CHAR and VARCHAR seem to be similar to BINARY and VARBINARY data types, they have some differences. BINARY and VARBINARY store binary strings, and length is measured in bytes.

ENUM Data Type in MySQL

The MySQL ENUM data types are strings with enumeration values. ENUM allows you to set a list of predefined values and then choose any of them. If you add an invalid value that is not included in the list, you will get an empty string.

For example, we want to create a table that will store information about the size of women’s clothes: small, medium, and large. In the table, we will insert the size column with the ENUM type. It means that this column will take only specified values.

-- create a table with the CREATE TABLE statement

CREATE TABLE clothes (
    product_ID int PRIMARY KEY AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    fabric text NOT NULL,
    size enum ('small', 'medium', 'large') NOT NULL
);

-- insert into the table a new row with a valid value

INSERT INTO clothes (product_ID, name, fabric, size)
  VALUES (1, 'dresses', 'cotton', 'small');

After that, retrieve data and see the result.

Now, insert another row with a value (‘extra large‘) that was not specified in the list and retrieve data.

-- add 'extra large' to the size column that is not included in the permitted values

INSERT INTO clothes (product_ID, name, fabric, size)
  VALUES (2, 'dresses', 'silk', 'extra large');

As you can see, the output displays an empty string for the invalid value of the ENUM data type column.

SET Data Type

The MySQL SET data types allow you to store zero or multiple values (separated by comma) you specified in a list of predefined values when creating a table. For example, suppose that customers can wear some dresses both in autumn and winter. In this case, we can insert in the aforementioned table clothes a new row season and assign the SET (‘autumn’, ‘winter’) data type to the column. In the output, we may see the following options:

''
'autumn'
'winter'
'autumn,winter'

MySQL Numeric Data Types (Number Formats)

MySQL supports numeric data types such as integers, decimals, and floating-point data types:

• Integers represent numbers without fractions and can have SIGNED and UNSIGNED attributes. Usually, they may be used for IDs or counting numbers.
• Decimals represent numbers with fractions and store exact numeric values in the column. They can be signed and unsigned and are usually used for columns that store monetary values. In the comparison with the floating-point numbers, decimals are more accurate.
• Floating-point represent numbers with fractions but do not store exact numeric values. They can be signed and unsigned. Floating-point numeric values use a double-precision 64-bit format or a single-precision 32-bit format to store data. They may lead to a loss of precision during arithmetic operations.

Integer Data Types

Integer data types are numeric values without fractions. MySQL supports the following integer data types:

• TINYINT
• SMALLINT
• INT
• MEDIUMINT
• BIGINT

They can be UNSIGNED, which allow only zero and positive numbers in a column, and SIGNED, which store zero, positive, and negative numbers. For more information about integer data types, see .

Boolean Data Type

The boolean data types can only accept either true or false values. In a binary format, true refers to 1 and false – to 0. As a rule, they are used for logical operations.

MySQL does not have a boolean (or bool) data type. Instead, it converts boolean values into integer data types (TINYINT). When you create a table with a boolean data type, MySQL outputs data as 0, if false, and 1, if true.

Float Data Type

The Float data types represent single-precision approximate numeric values that require 4 bytes for storage. They can have SIGNED and UNSIGNED attributes.

AttributeMinimum storage sizeMaximum storage sizeSIGNED-3.402823466E+38-1.175494351E-38UNSIGNED0 and 1.175494351E-383.402823466E+38

Note: Starting from MySQL version 8.0.17, UNSIGNED is deprecated for the FLOAT and DOUBLE data types.

When adding a column, you need to set values for the float data type in brackets – FLOAT(m,d) where ‘m‘ is the number of digits in total and ‘d‘ is the number of digits after the decimal point.

Double Data Type

The Double data types refer to the floating-point numeric data types and use 8 bytes to store double-precision values. The syntax for the double data type is DOUBLE PRECISION(m,d) where ‘m‘ is the total number of digits and ‘d‘ is the number of digits following the decimal point. For example, DOUBLE(7,5) means that it will store a value with seven digits and five decimals.

AttributeMinimum storage sizeMaximum storage sizeSIGNED-1.7976931348623157E+308-2.2250738585072014E-308UNSIGNED0 and 2.2250738585072014E-3081.7976931348623157E+308

Decimal Data Type

The DECIMAL data type can be used to store exact and fixed numeric values. When creating a table column, the syntax for the data type is DECIMAL(p,s) where ‘p‘ stands for precision, the maximum number of digits, and ‘s‘ stands for scale, the number of digits following the decimal.

As a result, the main difference between float and double data types is precision (from 0 to 23 for FLOAT, and from 24 to 53 for DOUBLE) and accuracy (up to approximately 7 decimals for FLOAT, and up to approximately 15 decimals for DOUBLE).

To sum up, decimals are better to use for fixed amounts, such as monetary and financial information (price, salary, etc,), while float and double – for approximate calculations where rounding values might have a negative impact.

BIT Data Type

The BIT data type is used to store binary values in the column and accepts either 0 or 1. The range of bit values for the column goes from 1 to 64. If the range is not set, the default value will be 1.

For example, create the working_hours table with the days column as BIT(7). In the days column, 1 serves as a working day and 0 – as a day-off.

CREATE TABLE working_hours (
  employee_id int NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  month varchar(8),
  week int,
  days bit(7),
  PRIMARY KEY (employee_id)
);

Suppose in April, employees can have two additional day-offs. Thus, insert a new row in the table using the INSERT INTO statement:

INSERT INTO working_hours (employee_id, first_name, last_name, month, week, days)
  VALUES (1, 'Jordan', 'Sanders', 'April', 2, B'1111100');

Retrieve data by executing the SELECT statement:

SELECT * FROM working_hours wh;

In the output, we can see that the result is not displayed as binary, and MySQL treats the BIT data type as integers – 124.

So, to retrieve data being converted to binary, we need to use the BIT() function and view the result.

SELECT employee_id, first_name, last_name, month, week, BIN(days) FROM working_hours wh

MySQL Date & Time Data Types

For managing date and time information in databases, MySQL date types are used that are divided into DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

TypeUsageData type formatRangeDATEStores only date information in the table columnYYYY-MM-DD format (year, month, and date)from ‘1000-01-01’ to ‘9999-12-31’TIMEDisplays only timeHH:MM:SS format (hours, minutes, and seconds)from ‘-838:59:59’ to ‘838:59:59’DATETIMEStores both date and time in the columnYYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds)from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’DATETIMEStores both date and time values in the columnYYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds)from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’TIMESTAMPStores both date and time values in the column. Conversion of the value from the zone of the connection server to UTC takes place.YYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds)from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTCYEARStores only year values in the columnYYYY (year)from ‘1901’ to ‘2155’

Date Data Type

Let’s see on a particular example how to create the employees table with information about employees, including the date of birth to which we declare the DATE type. Then, insert a new row into the table and retrieve data.

-- create the employees table

CREATE TABLE employees(
  employee_id INT AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  date_of_birth date,
  PRIMARY KEY (employee_id)
);

-- insert data into the table
INSERT INTO employees (employee_id, first_name, last_name, date_of_birth)
  VALUES (1, 'John', 'Sanders', '2000-01-19');

-- retrieve data
SELECT * FROM employees e;

The output is as follows:

DATETIME & TIMESTAMP

DATETIME and TIMESTAMP seem to be similar, though they have some differences. The DATETIME data type needs 4 bytes to store data, while the TIMESTAMP data type requires 8 bytes. Besides, DATETIME is a constant data type, and TIMESTAMP is temporal as it depends on the UTC zone.

Let’s add the date and time of the first day at work. In this case, we will use the DATETIME data type for the new column first_day_at_work.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_item VARCHAR(255) NOT NULL,
    use_by date,
    price int,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

0

The output is as follows:

Time Data Type

Now, let’s add information about the time the employee should start working every day. For this, update the employees table by adding the start_to_work column with the MySQL TIME data type for the value.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_item VARCHAR(255) NOT NULL,
    use_by date,
    price int,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1

The result should be as follows:

Year Data Type

Finally, we will add to the table the year when an employee was certified and declare the YEAR data type for the column.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_item VARCHAR(255) NOT NULL,
    use_by date,
    price int,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2

MySQL Spatial Data Types

Spatial data types store geometry and geography values in the table column. MySQL supports single geometry values (GEOMETRY, POINT, LINESTRING, POLYGON) and multiple values grouped in a set (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION).

The table provides a description per spatial data type.

TypeDescriptionGEOMETRYStores any type of the geometry valuePOINTStores a MySQL single X and Y coordinate valueLINESTRINGStores a set of points that form a curvePOLYGONStores a set of points in the multisided geometryMULTIPOINTStores a set of multiple point valuesMULTILINESTRINGStores a set of multiple LINESTRING valuesMULTIPOLYGONStores a set of multiple POLYGON valuesGEOMETRYCOLLECTIONStores a set of multiple GEOMETRY values

JSON Data Type

Starting with version 5.7.8, MySQL supports JSON data types that store JSON documents in the JSON column and provide access to document elements easily and quickly. The JSON format allows for a quick search of values within the document using a key or array index, which, in turn, will improve overall performance and optimize storage. In addition, JSON helps validate documents and in case of invalid values, you will get an error. The maximum size is 1GB.

How to Change Data Type in MySQL

Considering a list of MySQL data types discussed in the article, it can be important to change default data types so that they suit our needs best.

Let’s have a look at the example explaining how to change default data types in MySQL columns using dbForge Studio for MySQL which is a cutting-edge alternative to Workbench. The dbForge MySQL GUI tool provides a huge set of advanced features for database development, management, and deployment aimed at maximizing developers’ productivity and increasing overall performance.

To change a data type for the given column, use the ALTER statement. The syntax is as follows:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_item VARCHAR(255) NOT NULL,
    use_by date,
    price int,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3

For our example, we are going to use the employees table. To open the CREATE script of the table, in Database Explorer, right-click the table and select Generate Script As > CREATE > To New SQL Window.

In the SQL document that opens, change the data type of the first_day_at_work column using the ALTER statement. Then, execute the query by clicking Execute on the SQL toolbar.

Now, retrieve the data: In Database Explorer, right-click the table and select Retrieve Data.

Alternatively, you can refresh the list of tables in Database Explorer and see the changed data type.

Conclusion

In the article, we have reviewed MySQL data types with examples, such as string, numeric, date and time, spatial, and JSON, as well as explored data types each group includes. Besides, we exemplified how to change a data type in the MySQL table column with the help of dbForge Studio for MySQL.

You can also use the following articles to learn how to make the development process much faster and easier with dbForge Studio for MySQL:

• Migrate data from MySQL to PostgreSQL
• Create a database in MySQL
• Use SELECT statement in MySQL
• Use MySQL integer data types

Download a 30-day free trial version to assess the features of dbForge Studio for MySQL to be convinced that it is worth purchasing the full license of the tool in order to improve productivity and database development and management in the most efficient way.

How to declare a boolean variable in MySQL?

How do I insert a boolean value in SQL?

How to set default boolean value in MySQL?