November Black Friday C# week
Black friday is here! Get up to 80 % extra points for free! More info
Only this week up to 80 % off on C# courses. More info

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

Type Range
TINYINT 8bit number
SMALLINT 16bit number
MEDIUMINT 24b number
INT 32b
BIGINT 64b

If we specify the word UNSIGNED after the type, the numbers will be positive only, with the range of 0..2number 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

Type Range
TINYTEXT max. 255 B
TEXT max. 64 KB (this is really long text, perhaps an article)
MEDIUMTEXT max. 224 B
LONGTEXT max. 232 B
VARCHAR (max. number of characters) number of characters that you choose, max. 64 KB
CHAR (number of characters) fixed number of characters that you choose, max. 255

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

Type Range
DATE Date as a string in the format of 'yyyy-mm-dd', range from '1000-01-01' to '9999-12-31'
TIME Time as a string in the format of 'hh:mm:ss'
DATETIME Practically, it's the date and time concatenation: 'yyyy-mm-dd hh:mm:ss'
TIMESTAMP From the outside, it behaves just like DATETIME, but internally, it's a dword with the number of seconds elapsed since 1.1.1970. Its range ends in January 2038, so I don't recommend to use this type. I'm mentioning it just for completeness' sake.
BLOB and the like. General data in binary form, usage is analogous to the TEXT types. These allow us to store, for example, images or sounds into the database.

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:

Inserting a user with some values unspecified into the MySQL database

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.

Name Description
AUTO_INCREMENT Just for numbers. When inserting a new row, give this item a NULL value and the system automatically assigns a value of 1 greater than the previous row has (theoretically, the increment can be changed but we're not going to bother with it now). This is an excellent thing for creating unique identification keys easily.
UNIQUE Specifies that there must be no more rows with the same value in this column (except for the NULL values). It makes sense only for keys.
NOT NULL This value must not be empty - the NULL value cannot be inserted.
PRIMARY KEY This determines that this column (just one in each table) will be used as the key. It's appropriate for some relatively short identifiers that we can search by most often. The primary key is always NOT NULL and UNIQUE; even if we don't define it, it gets these properties implicitly.
DEFAULT value The default value that the item gets in case we don't specify its value when inserting a new row. It doesn't work for Texts, Blobs, and auto_increment items.

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 :)


 

 

Article has been written for you by David Capka
Avatar
Do you like this article?
No one has rated this quite yet, be the first one!
The author is a programmer, who likes web technologies and being the lead/chief article writer at ICT.social. He shares his knowledge with the community and is always looking to improve. He believes that anyone can do what they set their mind to.
Unicorn College The author learned IT at the Unicorn College - a prestigious college providing education on IT and economics.
Previous article
MySQL step by step: Ordering, Limit, and Aggregation
All articles in this section
MySQL database step by step
Thumbnail
Next article
MySQL Step By Step: Queries Over Multipe Tables (JOIN)
Activities (3)

 

 

Comments

To maintain the quality of discussion, we only allow registered members to comment. Sign in. If you're new, Sign up, it's free.

No one has commented yet - be the first!