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  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?
			
To deal with Boolean in MySQL, you can use BOOL or BOOLEAN or TINYINT[1]. If you use BOOL or BOOLEAN, then MySQL internally converts it into TINYINT[1]. In BOOL or BOOLEAN data type, if you use true literal then MySQL represents it as 1 and false literal as 0 like in PHP/ C/ C++ language.
		
			
How do I insert a boolean value in SQL?
			
You can insert a boolean value using the INSERT statement: INSERT INTO testbool [sometext, is_checked] VALUES ['a', TRUE]; INSERT INTO testbool [sometext, is_checked] VALUES ['b', FALSE]; When you select a boolean value, it is displayed as either 't' or 'f'.
		
			
How to set default boolean value in MySQL?
			
You have to specify 0 [meaning false] or 1 [meaning true] as the default. Here is an example: create table mytable [ mybool boolean not null default 0 ];