DB2 Vs MySQL series | comparison of data types between MySQL and DB2, db2mysql

DB2 Vs MySQL series | comparison of data types between MySQL and DB2, db2mysql

As MySQL Databases become more and more widely used, there are more and more demands for migration from DB2 to MySQL databases. During database migration, the first and most basic and most important thing is the conversion between the two database data types.

Related reading:

From commercial to open-source: analyzes the differences between DB2 and MySQL Databases in 15 dimensions

From commercial to open-source: Best practices for migrating DB2 to MySQL

The following describes the differences between the two types of database data and some precautions during the migration, in conjunction with the DB2 database migration project of the open source database research and testing project of Shenzhen Branch of China Securities classified settlement (Shenzhen.

Both the DB2 database and the MySQL database define a data type for each column when creating a database table to limit the value range of this column. The DB2 database supports built-in data types (built-in) and user-defined data types (UDT), but the MySQL database only supports built-in data types.

Is the built-in data type supported by the MySQL database:

MySQL5.7Overview of supported data types

Is the built-in data type supported by the DB2 database:

DB2 V9/V10Overview of supported data types

DB2 data type conversion to MySQL DATA type can be divided into three categories in principle. In the actual conversion process, we recommend that you follow the following conversion rules for conversion:

Numeric field type (Numeric)

The numeric types of the DB2 database and MySQL database can be classified into integer and floating point values. In terms of integer values, the MySQL database has two types of integer values: singed and unsigned. Therefore, each type can have two precision ranges; DB2 databases are all signed values with only one precision range. Is the integer type table of the two databases:

For floating point numeric conversion, the basic data types in DB2 can be found in MySQL with the same name.

The actual test result shows that the DB2 value type is basically synonymous with the value type corresponding to MySQL, and can be easily converted.

Date and Time field type (Date & Time)

MySQL supports more Date and Time types (Datetime/Date/Timestamp/Time/Year) than DB2 supports (Date/Timestamp/Time.

The two types of databases have the following similarities and differences in date and time:

Date type

The Date type in DB2 and MySQL occupies four bytes (the first two bytes are Year, the third byte is Month, and the fourth byte is Day ); the main difference is that the Year range in the DB2 database is 0001 ~ 9999, the range of Year in MySQL is 1000 ~ 9999. It can be directly converted in practical use.

Timestamp type

Timestamp in MySQL is a combination of Date and time, which ranges from 2037 00:00:00 to January 1. The conversion can be directly converted from the Timestamp in DB2.

Time Type

In MySQL, the value range of the Time type is from "-838: 59: 59" to "838: 59: 59" (HHH: MM: SS ), the hour is very large because the Time type in MySQL not only represents the Time in a day (less than 24 hours ), it can also represent the time consumed by a database event or the interval between two events (which may be greater than 24 hours or even a negative value ).

In DB2, this Time type refers to the Time in a day, so the value range can only be from "00:00:00" to "23:59:59 ".

Others

In MySQL, apart from the preceding three Date and Time types, there are two special time types: Datetime and Year. Datetime is also a combination of the date and time types (YYYY-MM-DDHH: MM: SS), where Year ranges from 1000 to 9999.

The value range Of The Year type is from 1901 to 2155. SMALLINT or CHAR (4) can be used in actual use.

String Field Type (String)

CHAR/VARCHAR

Whether it is DB2 or MySQL, CHAR (N) And VARCHAR (N) Represents the Fixed Length and variable length character types, respectively.NThe maximum number of characters that can be defined. These two data types can be exchanged in DB2 and MySQL.

However, in DB2, CHAR (N) And VARCHAR (N), Such as CHAR (N) For bit data, VARCHAR (N) For bit data is not well implemented in MySQL. Instead, MySQL designs a new DATA type to implement similar functions in DB2. MySQL uses BINARY (N) And VARBINARY (N) Replace CHAR (N) For bit data and VARCHAR (N) For bit data.

In the DB2 database, use the For Bit Data clause to modify Char (N) And Varchar (N) Restrict the specified field type to store binary data, which is generally used to store audio data. In MySQL databases, Char and Varchar types are not similar extensions. Instead, BINARY and VARBINARY data types are designed to store BINARY data.

 

Big Data Field (LOB) Type

The maximum number of bytes that can be stored for VARCHAR fields in the DB2 database cannot exceed the page size defined in the table. For example, the page size (pagesize) of a table is 32KB, the defined VARCHAR has a maximum length of 32,672 bytes. If the length of the byte to be stored exceeds the page size of the table, you need to define the big data storage data for the field.

DB2 has two types of big data: CLOB and BLOB, which store character or binary data respectively. In MySQL, the definition of big data fields is more refined. Different Levels of field types are designed for big data fields of different lengths.

As shown in, CLOB (N) And BLOB (N) The two big data field types correspond to the eight data types in the MySQL database because of their defined sizes.

Auto-increment Field Type

DB2 allows you to specify only one field in the table as the Identity auto-increment field when creating table, which can be Generatedalways and Generated bydefault, respectively, indicates that the defined auto-increment field does not allow manual (Application) intervention or manual intervention.

The auto-increment field in the MySQL database only needs AUTO_INCREMENT modification, and this field is manual intervention.

Summary

The above mainly compares some differences in the Basic Data Types Between the DB2 database and the MySQL database. Understanding these differences is essential for migrating the DB2 database to the MySQL database. To facilitate the comparison, the Data Types of the two databases are summarized as follows:

Recommended reading:

From commercial to open-source: Best practices for migrating DB2 to MySQL

Analyzes the differences between DB2 and MySQL Databases in 15 dimensions

From Oracle to MySQL, the most important thing is...

Non-technical differences between MySQL and Postgre SQL

Powered by MariaDB, MySQL is more popular than Oracle

Download Resources

Public Account: Data and cloud (OraNews) reply keyword acquisition

'Dedicated DTC', Dig DTC Conference PPT

'Dbalife', "DBA's Day" Poster

'Dba04', DBA Manual 4, classic chapter ebook

'Internal', Oracle RAC PPT

'122arch', Oracle 12.2 architecture Diagram

'Drawing oow', Oracle OpenWorld documents

'Prection', Lecture hall Course Materials