Course 12: Data Type in SQL

Oct 2, 2021
Course 12: Data Type in SQL

Data types

SQL recognizes four main data types, which are further subdivided into subtypes. These four types are:

  • Numbers: subdivided into whole or decimal numbers, themselves of floating or exact type.
  • The character strings. Character strings must always be associated with an encoding type, which regulates, among other things, the number of bytes used to encode each character. A character type can be of fixed or variable size. Typically, database servers place a limit on the size of a string, on the order of a few thousand. This limit can be expressed in bytes or in number of characters. If we want to store very large strings, we will use a binary type.
  • The date type, rather called the time type. There are several types of time, depending on whether you want to record a precise date to the hour, or to the day (among others).
  • The binary type. This type is used to store purely binary information (images for example), or of very large size. This type is often called blob(acronym for binary large object ) or sometimes (acronym for character large object ). clob

3.1. Numeric types

Numeric types are divided into two parts: exact types and approximate types. Exact types cover integers and fixed-point numbers (which formally are also integers), and approximate types are the floatand the doubleof classical programming languages.

The following table groups together the integer numeric types.

Table 5. Integer numeric types

Type SQL Coding
tinyint 8 bits
smallint 16 bits
integer Where int 32 bit
bigint 64 bits

The type numericallows you to set the number of digits of the numbers that you want to store, and the number of digits after the decimal point of these digits. So numeric(p, s)pas precision and sas scale) defines a number with pdigits and sdigits after the decimal point. The value of sis optional, it is 0 by default.

Finally, the approximate types are floatand double, in a classical way.

3.2. Character types

The encoding of characters is a problem in itself, particularly complex because of the great profusion of the number of characters used in the world, and their classification. A partial response to the limitations of the ASCII code (on 8 bits) has been provided by the introduction of Unicode. Unicode proposes to encode the characters on a variable number of bytes, which can go from a single (in order to maintain compatibility with ASCII), up to 4, in order to support all existing combinations of diacritics.

All recent database servers support Unicode (at least UTF-8). The definition of text encoding is then done at several levels. Usually there is a default encoding set at the database level, which can be overloaded at each table level and then overloaded again at each column level.

Added to all this is the method of comparing strings. A simple alphabetical sorting can hide a real complexity. Do we take into account the lowercase / uppercase differences? How do you compare an accented character and the same character without an accent, or with a different accent? It is possible to set so-called collation rules , which precisely fix the way in which these strings are compared.

All of the types presented here support character set definition, and collation.

3.2.1. The type char

The type charallows you to store character strings of fixed size, between 0 and 255. If a string shorter than the indicated size is saved, then the server automatically adds spaces at the end of the string.

3.2.2. The type varchar

The type is varcharused to store character strings of variable size, between 0 and 65535. No space is added if a string shorter than the specified size is saved.

3.2.3. The type text

The type is textused to store large texts. There are four types in MySQL: TinyTextTextMediumTextand LongText.

The following table gives the text sizes that can be stored in these different fields. Note that this size is given in bytes and not in number of characters. Depending on the encoding used, the number of characters stored may therefore vary.

Table 6. Type sizes textfor MySQL

Text type cut
tinytext 8 bytes
text 16 bytes
mediumtext 24 bytes
longtext 32 bytes

3.3. Temporal types

The management of dates in SQL is recognized as one of the most complex and thorny subjects in the language. We’re going to take it in the simplest way possible, trying to make things accessible.

First, there are three different types for recording dates: timedateand timestamp.

3.3.1. Type date

One dateis made up of three elements:

  • a year, coded on a four-digit integer;
  • a month, encoded as an integer from 1 to 12;
  • a day, encoded as an integer from 1 to 31.

In MySQL, the type dateallows you to encode dates from '1000-01-01'to '9999-31-12'.

3.3.2. Type time

The type timeis also made up of three elements:

  • one hour, encoded as an integer from 0 to 23;
  • one minute, encoded as an integer from 0 to 59;
  • one second, encoded as an integer from 0 to 59.

In MySQL, the type timeallows you to encode dates over a much wider range, going from -838:59:59to 838:59:59. Indeed this type is used to encode both the hours of the day, but also time intervals expressed in hours.

3.3.3. Type datetimeand timestamp

These last two types are compositions of one dateand one time. They therefore allow a date to be encoded to the nearest second.

In MySQL, there is a sneaky difference between these two types:

  • the type datetimeallows you to encode dates from 1000-01-01 00:00:00to 9999-12-31 23:59:59;
  • the type timestampallows you to encode dates from 1970-01-01 00:00:01to 2038-01-19 03:14:07.

We will therefore be aware of the limitations of these different types when building a database.

3.3.4. Time functions

The last difficulty that can be encountered when handling dates comes from the very nature of these data. A date can only be handled through its representation, in the form of a character string. If we try to insert a date into a table, by giving only the character string that represents it, we have every chance of getting a rather obscure error message, and in any case of little use (it is is in any case the case under Oracle and MySQL)!

Switching from a date to its representation as a string is automatic, so the display will never be a problem. However, the opposite is problematic. Under MySQL you must use the following conversion functions:

  • date(): convert a string to date, ex: date('1918-11-11')
  • time(): convert a string to time, ex: time('10:25:45')
  • timestamp(): convert a string to datetime, ex: timestamp('1918-11-11 10:25:45')

A data insertion or update command must therefore use these conversion functions in order to function.

All database servers offer a large list of functions allowing you to manipulate dates: search for the name of a day, the last day of a month, arithmetic on dates, etc. for the exhaustive list.

Finally, let us quote the temporal functions of MySQL which make it possible to obtain the current date:

  • curdate(): returns the present moment in the form of date;
  • curtime(): returns the present moment in the form of time;
  • now(): returns the present moment in the form of datetime.

All of these functions can be called in a select, and used as an argument of a insertor a update.

3.4. Binary types

The binary type is declared with the keyword blob(binary large object). In MySQL, there are four types of blobs, corresponding to the four types of large text, and of the same size:

Table 7. Size of types blobfor MySQL

Type blob cut
tinyblob 8 bytes
blob 16 bytes
mediumblob 24 bytes
longblob 32 bytes

Unlike text, the blobcannot be associated with character sets, and is only comparable numerically. The blobare used to store arrays of bytes , such as images, or large amounts of data.

3.5. MySQL auto-incremental type

MySQL has since its inception introduced a data type of its own, intended to compensate for the lack of sequence. An auto-incremental column is declared as follows.

Example 30. Creating an auto-incremental column in MySQL

create   table Sailors (
   id  int auto_increment  primary  key, 

You cannot explicitly insert data in an auto-incremental column. On the contrary, MySQL automatically maintains the value of this column, and guarantees us that within the same table two rows will not take the same value.

Auto-incremental columns are a convenient way to create primary keys.

3.6. Self-generated type of Derby

Derby exposes the same functionality, on a similar principle, but with a different syntax. Derby’s auto-generated columns can only be of three types:

  • INT

A self-generated column can then be of type GENERATED ALWAYS, or GENERATED BY DEFAULT. In the first case, it is not possible to insert a value in this column; Derby is responsible for filling it. In the second case, Derby allows the manual insertion of values, and generates one automatically if no value is supplied.

It is also possible to specify the way in which the generated values ​​are produced: the incrementation step, etc …

Example 31. Creating a column generated with Derby

create   table Sailors (
   id  int  primary  key generated always 
           as identity (start  with 1000, increment  by 100), 

3.7. Oracle Sequences

The approach offered by Oracle is different. Rather than generating automatic values ​​in a table, Oracle exposes the notion of sequence . A sequence is a particular object of a database, just like a table, or a constraint.

We create a sequence using the command create sequence seq, in which seqrepresents the name of the sequence we are creating. We can thus create as many sequences as we want.

A sequence maintains two values:

  • curval : the current value of this sequence;
  • nextval : the next value of this sequence;

The contract for these two values ​​is as follows:

  • a call to curvalalways returns the value returned by the last call to nextval;
  • a call to nextvalalways returns a new value, generally obtained by incrementing the current value.

The guarantee that two successive calls to nextvalnever return the same value is very strong, and allows the use of sequences to generate primary keys. Note, however, successive calls to nextvalin the same selectreturn the same value.

The advantage of sequences compared to auto-incremental columns is that it becomes possible to create unique primary keys within, no longer a table, but a set of tables, or even a complete database. . This makes it possible to simplify things when we want to reorganize a database, by merging tables in particular.

Finally, note that Oracle does not expose the notion of auto-incremental column. It is possible to simulate this behavior at the level of a column by creating a trigger .

Example 32. Sequences in Oracle

- creation of a 
 create sequence marin_seq;

 - creation of a new 
 select marin_seq.nextval   from   dual value ;

 - reading of the current value 
 select marin_seq.curval   from   dual ;

Leave a Reply

Your email address will not be published. Required fields are marked *