Lesson 6 - MySQL step by step: Data Types and NULL

In the previous lesson, MySQL step by step: Ordering, Limit, and Aggregation, we learned about ordering results and aggregation functions. Today, we're going to get back to the data types, introduce the most important ones, and learn something about the NULL value.

Data types

At the beginning of the course, we listed several basic data types. Back then, I didn't want to bother you with them too much. We talked about the INT, VARCHAR, and DATE data types. The database (specifically MySQL) offers, of course, much more of them. Let's take a look at few tables and introduce the most important ones. The following tables contain the most commonly used data types.

Integers

If we specify the word UNSIGNED after the type, the numbers will be positive only, with the range of 0..2^{number of bits}. If we don't specify anything extra, the numbers will be both positive and negative and the range shifted by half to the negative direction (e.g., instead of 0..255 it'll be -127..128).

Note: if you're looking for a boolean data type (the true/false values), the TINYINT (0/1) is typically used to store this kind of values.

Texts

In ASCII texts, the specified maximum lengths are really the number of characters (since 1 character = 1 B). In the Unicode encoding, one character may take more than one byte.

Other

NULL value

Data types in databases are little bit different from data types, as we know them from programming languages. While in the C language, an int can store values from -32,000 to +32,000 only and nothing else, the database INT can also hold the NULL value. NULL has nothing to do with zero (0), it indicates that the value hasn't yet been specified. The database philosophy is built in this way, the values which users didn't enter have the NULL value (unless we set the default value to something else), and each data type can be, in addition to the values we'd expect, of the NULL value. We can also forbid this value for a particular data type, we'll see this further on.

From the MySQL version 5.7, you must either enter values for all the columns when inserting a new row, or the table have to have the default values defined for the columns that we don't specify values for in the query. Otherwise, the query would end with the error message #1364 - Field 'xxx' doesn't have a default value. In older versions, when inserting data and not specifying some column, MySQL automatically used the default value for the given data type. If the column was nullable, it used NULL, otherwise a string would be an empty string, a number would be zero, etc. Because it happened a lot that people forgot to define a column and weren't notified, that behavior is no longer supported.

For example, if we insert a user with the INSERT command and provide some values only, NULL will be inserted into the other values. Let's try it:

INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Mr.', 'Incomplete');

The result:

We created the user table using an SQL query, CREATE TABLE. If the table is created using phpMyAdmin, it disables the NULL value in its columns and doesn't set the default values, so we'd need to enable NULL and set it as the default value to make the code above work. This can be changed at any time by editing a specific column in the Structure tab.

Benefits of the NULL value

Perhaps you ask what is the NULL value actually for? Its benefit is that we can say whether the value has been entered or not. Imagine entering a number, there's no value according to which we'd be sure whether the number hasn't yet been entered. If we set the value to zero (0), we wouldn't know whether the user has entered zero or hasn't entered the value at all. And also, NULL saves space in the database where, unlike the default values, it doesn't occupy any memory.

NULL on the application side

We've already said that programming languages usually don't support the NULL value (the statically typed ones). In dynamic languages like PHP, we don't use the data types at all, even though it's good to know that we can ask for NULL when we need it. In typed languages, such as Java or C#, we need to use other data types. In C#, we can make any data type NULLable, so the language understands it can also become NULL. In Java, we use the wrapper data types with uppercase letters, i.e. for numbers instead of int we'd use Integer.

Additional information about data types

For data types (columns, if you want), we can provide some more information. We've already used AUTO_INCREMENT. Let's look at others.

We write these new keywords modifying the data type after it, just like with AUTO_INCREMENT. Here's an example:

CREATE TABLE `user` (
    user_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(35) NOT NULL,
    PRIMARY KEY (user_id)
);

In the next lesson, MySQL Step By Step: Queries Over Multipe Tables (JOIN), we'll prepare tables and test data for a simple content management system such as we use here in ICT.social. In further lessons, we'll make queries through multiple tables and subqueries as well. So your arsenal of basic database tools will be complete for creating literally any application