Migrating Oracle users to Cloud SQL for MySQL: Data types, users, and tables

This document is part of a series that provides key information and guidance related to planning and performing Oracle® 11g/12c database migrations to Cloud SQL for MySQL version 5.7, second-generation instances. The series includes the following parts:

Migrating Oracle users to Cloud SQL for MySQL: Terminology and functionality
Migrating Oracle users to Cloud SQL for MySQL: Data types, users, and tables (this document)
Migrating Oracle users to Cloud SQL for MySQL: Queries, stored procedures, functions, and triggers
Migrating Oracle users to Cloud SQL for MySQL: Security, operations, monitoring, and logging

Data types

MySQL provides multiple data types that are fully equivalent or similar to the Oracle provided data types. The following table lists the most common MySQL data types, followed by a comparison between Oracle primitive data types and the corresponding MySQL data types; where a data type is not supported, an alternative data type is listed. Note that a MySQL 5.7 database has a limit of 65,535 bytes, for the entire row (depending on the used character set).

MySQL 5.7 primitive data types

MySQL data type family	MySQL data type name	Data type specification
String/ character	CHAR(n)	Stores exactly `n` characters.
	VARCHAR(n)	Stores a variable number of characters, up to a maximum of `n` characters.
	BINARY	Stores exactly `n` bytes.
	VARBINARY(n)	Stores a variable number of characters, up to a maximum of `n` bytes.
	BLOB	Binary large object that can hold a variable amount of data.
	TEXT	Specific variant of `VARCHAR` that does not require you to specify an upper limit on the number of characters.
	ENUM	String object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification when the table is created.
	SET	String object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created.
Numeric	INT	Min value is -2147483648 \| Max value is 2147483647.
	INTEGER	Min value is -2147483648 \| Max value is 2147483647.
	TINYINT	Min value is -128 \| Max value is 127.
	SMALLINT	Min value is -32768 \| Max value is 32767.
	MEDIUMINT	Min value is -8388608 \| Max value is 8388607.
	BIGINT	Min value is -2^63 \| Max value is 2^63-1.
	DECIMAL(p,s)	Able to store any value with `p` digits and `s` decimals.
	NUMERIC(p,s)	Able to store any value with `p` digits and `s` decimals.
	FLOAT(m,d)	Values can be stored with up to `m` digits in total, of which `d` digits may be after the decimal point.
	DOUBLE(m,d)	Values can be stored with up to `m` digits in total, of which `d` digits may be after the decimal point.
	BIT(m)	Storage of `m`-bit values. `m` can range from 1 to 64.
Date & time	DATE	- Values with a date part but no time part. - Retrieves and displays `DATE` values in `'YYYY-MM-DD'` format. - The supported range is `'1000-01-01'` to `'9999-12-31'`.
	DATETIME	- Values that contain both date and time parts. - Retrieves and displays `DATETIME` values in `'YYYY-MM-DD HH:MM:SS'` format. - The supported range is `'1000-01-01 00:00:00'` to `'9999-` `12-31 23:59:59'`.
	TIMESTAMP	- Values that contain both date and time parts. - `TIMESTAMP` has a range of `'1970-01-01 00:00:01'` UTC to `'2038-01-19 03:14:07'` UTC.
	TIME	- Values can range from `'-838:59:59'` to `'838:59:59'`. - The hours part might be so large because the `TIME` type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which might be much greater than 24 hours, or even negative).
	YEAR	- `YEAR` 1-byte type used to represent year values. - Can be declared as `YEAR` or `YEAR(n)` and has a display width of `n` characters.
JSON	JSON	Textual JSON data as a data type.
Spatial (Geometry)	GEOMETRY	The column type to specify when you want to use the data models that follow in the rest of this table.
	POINT	An `(x,y)` value.
	LINESTRING	A line `(pt1, pt2)`.
	POLYGON	A sequence of points, effectively a closed path.
	MULTIPOINT	Collection of `POINT` values.
	MULTI-LINESTRING	Collection of `LINE` values.
	MULTIPOLYGON	Collection of `POLYGON` values.
	GEOMETRY-COLLECTION	Collection of geometry data types.
Logical	BOOLEAN	- Holds a true or false value. - Accepts values such as `TRUE`, `'1'`, and `1` as true. - Uses 1 byte of storage, and can store `NULL`.

Oracle to MySQL data type conversion

Oracle data type family	Oracle data type name	Oracle data type specification	MySQL to Oracle equivalent	MySQL corresponding/alternative
String/ Character	CHAR(n)	Maximum size of 2000 bytes.	Yes	CHAR(n)
	CHARACTER(n)	Maximum size of 2000 bytes.	Yes	CHARACTER(n)
	NCHAR(n)	Maximum size of 2000 bytes.	Yes	NCHAR(n)
	VARCHAR(n)	Maximum size of 2000 bytes.	Yes	VARCHAR(n)
	NCHAR VARYING(n)	Varying-length UTF-8 string Maximum size of 4000 bytes.	Yes	NCHAR VARYING(n)
	VARCHAR2(n) 11g	Maximum size of 4000 bytes Maximum size of 32 KB in PL/SQL.	No	VARCHAR(n)
	VARCHAR2(n) 12g	Maximum size of 32767 bytes `MAX_STRING_SIZE=EXTENDED`.	No	VARCHAR(n)
	NVARCHAR2(n)	Maximum size of 4000 bytes.	No	VARCHAR(n)
	LONG	Maximum size of 2 GB.	Yes	LONG
	RAW(n)	Maximum size of 2000 bytes.	No	VARBINARY(n)
	LONG RAW	Maximum size of 2 GB.	No	LONGTEXT
Numeric	NUMBER	Floating-point number.	No	NUMERIC/DECIMAL(p,s)
Numeric	NUMBER	Floating-point number.	No	NUMERIC/DECIMAL(p,s)
	NUMBER(*)	Floating-point number.	No	DOUBLE
	NUMERIC(p,s)	Precision can range from 1 to 38.	Yes	NUMERIC(p,s)
	FLOAT(p)	Floating-point number.	Yes	FLOAT(p)
	DEC(p,s)	Fixed-point number.	Yes	DEC(p,s)
	DECIMAL(p,s)	Fixed-point number.	Yes	DECIMAL(p,s)
	INT	38-digit integer.	Yes	INT
	INTEGER	38-digit integer.	Yes	INTEGER
	SMALLINT	38-digit integer.	Yes	SMALLINT
	REAL	Floating-point number.	Yes	REAL
	DOUBLE PRECISION	Floating-point number.	Yes	DOUBLE PRECISION
Date & Time	DATE	Stores date and time data (year, month, day, hour, minute, and second).	Yes	DATE
	TIMESTAMP(p)	Date and time with fraction.	Yes	TIMESTAMP
	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone.	No	DATETIME(n)
	INTERVAL YEAR(p) TO MONTH	Date interval.	No	VARCHAR(n)
	INTERVAL DAY(p) TO SECOND(s)	Day and time interval.	No	VARCHAR(n)
Logical	BOOLEAN	Values `TRUE`, `FALSE`, and `NULL`. Cannot be assigned to a database table column.	Yes	BOOLEAN
XML	XMLTYPE	XML data.	No	LONGTEXT
LOB	BFILE	Pointer to binary file, with a maximum size of 4 GB.	No	VARCHAR(255)
	CLOB	Character large object with a maximum file size of 4 GB.	No	LONGTEXT
	BLOB	Binary large object with a maximum size of 4 GB.	Yes	BLOB
	NCLOB	Variable-length Unicode string with a maximum file size of 4 GB.	No	LONGTEXT
ROWID	ROWID	Physical row address.	No	CHAR(n)
	UROWID(n)	Universal row ID of the logical row addresses.	No	VARCHAR(n)
Spatial	SDO_GEOMETRY	The geometric description of a spatial object.	No	N/A
	SDO_TOPO_GEOMETRY	Describes a topology geometry.	No	N/A
	SDO_GEORASTER	A raster grid or image object is stored in a single row.	No	N/A
Media types	ORDDicom	Supports the storage and management of audio data.	No	N/A
	ORDDicom	Supports the storage and management of Digital Imaging and Communications in Medicine (DICOM).	No	N/A
	ORDDoc	Supports storage and management of any type of media data.	No	N/A
	ORDImage	Supports the storage and management of image data.	No	N/A
	ORDVideo	Supports the storage and management of video data.	No	N/A

User-defined types

Oracle refers to user-defined types (UDTs) as OBJECT TYPES, which are managed using PL/SQL. User-defined types let the user create application-dedicated, complex data types that are based on and extend from the built-in Oracle data types list.

Here's an Oracle example of UDT:

SQL> CREATE OR REPLACE TYPE EMP_PHONE_NUM AS OBJECT (
     PHONE_NUM VARCHAR2(11));

Conversion notes

MySQL does not allow users to create dedicated, defined types.

Users and tables

This section discusses creating users and assigning them permissions, and the necessity of converting Oracle tables into Cloud SQL for MySQL tables.

User creation and permissions

Oracle database user accounts (Oracle "user" and "schema" are identical) can be used for authenticating and connecting to database sessions, while authorization access is set at each user's individual level for specific database objects and permissions.

In general, there are two types of database users:

Administrators: managing the database instance, users, and resources.
User Accounts: serving logical operations such as applications.

Administrators grant privileges to user and application user accounts in order to access the database objects. Oracle database permissions are granted to a user for specific operations (for example, create session/connect) or specific database objects (for example, SELECT on a specific table or EXECUTE on a specific stored procedure).

Conversion considerations

The MySQL CREATE USER syntax is different from Oracle's and cannot be migrated as is. Also, both databases have different user architecture.
When an admin creates a user in MySQL, they must specify the server that the user needs to connect to for database access. The server can be either a specific IP address or DNS, or it's allowed to connect from all sources by using the wildcard % sign).
Because the MySQL user name is a two-part attachment between the user name and the server from which the user would be allowed to connect from, a user can be created with the same name but from a different server as the client address (IP/DNS).
Once the user is created, it can be granted permission to database objects that are a part of a specific database or schema.
Users can also be granted additional permissions according to Cloud SQL for MySQL allowed permissions (for example, the root user will have all privileges except for the SUPER and FILE privileges). Oracle users can be attached to a dedicated tablespace while in MySQL, so this functionality is not relevant.

Tables

Oracle tables are constructed from many elements such as column data types, table constraints, indexes, partitions, Oracle tables proprietary features, and more. To successfully migrate into Cloud SQL for MySQL database tables, all Oracle table elements must be converted into MySQL tables. Some elements are supported with no or minor modifications, while some elements must be modified completely.

From a migration perspective, converting PL/SQL into MySQL probably requires more effort because converting Oracle tables into MySQL tables is a crucial stage with significant importance and further implications on performance and data size.

The following are the main differences between Oracle and MySQL tables and related features. These differences are discussed in the remaining parts of the series.

Case sensitivity (table and column names)
Create table syntax
Table and index metadata
Constraints support
Data types support and limitations
Indexes
Partitions and partitions management
Table and index maintenance
Temporary tables
Views
Visible and invisible columns (Oracle 12c)
Table and column character sets