This document discusses the basic query differences between Oracle® and Cloud SQL for MySQL, and how features in Oracle map to features in Cloud SQL for MySQL. It also outlines performance considerations for Cloud SQL for MySQL and ways to analyze and optimize query performance on Google Cloud. While this document touches on techniques to optimize stored procedures and triggers for Cloud SQL for MySQL, it does not cover how to translate PL/SQL code to MySQL stored procedures and functions.
When converting queries from Oracle Database to Cloud SQL for MySQL, there are certain SQL dialect differences to consider. There are also several built-in functions that are different or incompatible between the two database platforms.
Basic query differences
While both Oracle and Cloud SQL for MySQL support ANSI SQL, there are several fundamental differences when querying data, mainly around the use of system functions.
The following table highlights the differences in the SELECT and FROM
syntax for Oracle and Cloud SQL for MySQL.
| Oracle feature name | Oracle implementation | Cloud SQL for MySQL support | Cloud SQL for MySQL equivalent |
|---|---|---|---|
| SQL basic syntax for data retrieval |
SELECTFROMWHEREGROUP BYHAVINGORDER BY
|
Yes |
SELECTFROMWHEREGROUP BYHAVINGORDER BY
|
SELECT
for output print |
SELECT 1 FROM DUAL
|
Yes |
SELECT 1or SELECT 1 FROM DUAL
|
| Column aliases | SELECT COL1 AS C1
|
Yes |
SELECT COL1 AS C1or SELECT COL1 C1
|
| Table name case sensitivity | No case sensitivity
(for example, table name can be orders and
ORDERS) |
No | Case sensitive according to the defined table name (for example table name
can be only orders or ORDERS) |
Inline views
Inline views
(also known as derived tables) are SELECT statements, located in the FROM
clause, and used as a subquery. Inline views can help make complex queries
simpler by removing compound calculations or eliminating join operations, while
condensing multiple separate queries into a single, simplified query.
The following example outlines a conversion example from Oracle 11g/12c to Cloud SQL for MySQL for an inline view.
An inline view in Oracle 11g/12c:
SELECT FIRST_NAME,
DEPARTMENT_ID,
SALARY,
DATE_COL
FROM EMPLOYEES,
(SELECT SYSDATE AS DATE_COL FROM DUAL);
A working view in Cloud SQL for MySQL 5.7 with an alias:
SELECT FIRST_NAME,
DEPARTMENT_ID,
SALARY,
DATE_COL
FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL) AS A1;
Joins
Oracle's
join
types are supported by Cloud SQL for MySQL except for the FULL JOIN.
Cloud SQL for MySQL joins support the use of alternate syntax, such as the
USING clause, the WHERE clause instead of the ON clause, and the
SUBQUERY in the JOIN statement.
The following table shows a JOIN conversion example.
Oracle JOIN type |
Cloud SQL for MySQL support | Cloud SQL for MySQL JOIN syntax
|
|---|---|---|
INNER JOIN |
Yes |
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
|
CROSS JOIN |
Yes |
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D
|
FULL JOIN |
No | Consider using UNION with LEFT and
RIGHT JOINS:
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
|
LEFT JOIN
[ OUTER ]
|
Yes |
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
|
RIGHT JOIN
[ OUTER ]
|
Yes |
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
|
SUBQUERY |
Yes |
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
|
While Cloud SQL for MySQL supports both UNION and UNION ALL functions,
it does not support Oracle's INTERSECT and MINUS functions:
UNIONattaches the result sets of twoSELECTstatements after eliminating duplicate records.UNION ALLattaches the result sets of twoSELECTstatements without eliminating duplicate records.INTERSECTreturns the intersection of twoSELECTstatements, only if a record exists in the result sets from both queries.MINUScompares two or moreSELECTstatements, returning only distinct rows from the first query that are not returned by the second query.
The following table shows some Oracle to Cloud SQL for MySQL conversion examples.
| Oracle function | Oracle implementation | Cloud SQL for MySQL support | Cloud SQL for MySQL equivalent |
|---|---|---|---|
UNION |
SELECT COL1 FROM TBL1UNIONSELECT COL1 FROM TBL2
|
Yes | SELECT COL1 FROM TBL1UNIONSELECT COL1 FROM TBL2
|
UNION ALL |
SELECT COL1 FROM TBL1UNION ALLSELECT COL1 FROM TBL2
|
Yes | SELECT COL1 FROM TBL1UNION ALLSELECT COL1 FROM TBL2
|
INTERSECT |
SELECT COL1 FROM TBL1INTERSECTSELECT COL1 FROM TBL2
|
No |
SELECT COL1 FROM TBL1WHERE COL1 IN(SELECT COL1 FROM TBL2)
|
MINUS |
SELECT COL1 FROM TBL1MINUSSELECT COL1 FROM TBL2
|
No | SELECT A.COL1
FROM TBL1 A LEFT JOIN TBL2 B
ON USING(COL1)
WHERE B.COL1 IS NULL
|
Scalar and group functions
Cloud SQL for MySQL provides an extensive list of scalar (single-row) and aggregation functions. Some of Cloud SQL for MySQL functions are similar to their Oracle counterparts (by name and functionality, or under a different name but with similar functionality). While some Cloud SQL for MySQL functions can have identical names to their Oracle counterparts, they can also exhibit different functionalities.
The following table describes where Oracle and Cloud SQL for MySQL character functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
CONCAT |
Returns the first string concatenated with the second string: CONCAT('A', 1) = A1
|
Yes | CONCAT |
CONCAT('A', 1) = A1 |
CONCAT USING PIPE |
FNAME |' '| LNAME |
No | CONCAT |
CONCAT(FNAME, ' ', LNAME) |
LOWER or UPPER |
Returns the string, with all letters in lowercase or in uppercase:LOWER('SQL') = sql
|
Yes | LOWER or UPPER |
LOWER('SQL') = sql |
LPAD/RPAD |
Returns expression1, left or right padded to
length n characters with the sequence of characters in
expression2:LPAD('A',3,'*') = **A
|
Yes | LPAD or RPAD |
LPAD('A',3,'*') = **A |
SUBSTR |
Returns a portion of the string, starting at position
x (in this case 3), with a length of
y. The first position in the string is 1.
SUBSTR('MySQL', 3, 3)
= SQL
|
Yes | SUBSTR |
SUBSTR('MySQL', 3, 3)
= SQL
|
INSTR |
Returns the position (index) of a string from a given string:
INSTR('MySQL', 'y')
= 2
|
Yes | INSTR |
INSTR('MySQL', 'y')
= 2
|
REPLACE |
Returns a string with every occurrence of a search string replaced with a
replacement string:
REPLACE('ORADB', 'ORA', 'MySQL')
= MySQLDB
|
Yes | REPLACE |
REPLACE('ORADB', 'ORA', 'MySQL')
= MySQLDB
|
TRIM |
Trim leading or trailing characters (or both) from a string:
TRIM(both '-' FROM '-MySQL-')
= MySQL
TRIM(' MySQL ')
= MySQL
|
Yes | TRIM |
TRIM(both '-' FROM '-MySQL-')
= MySQL
TRIM(' MySQL ')
= MySQL
|
LTRIM/RTRIM |
Removes from the left side or right side of a string all characters that
appear in the search:
LTRIM(' MySQL', ' ')
= MySQL
|
Partially | LTRIM or RTRIM |
Oracle LTRIM and RTRIM functions take a second
parameter that specifies which leading or trailing characters to remove
from the string. Cloud SQL for MySQL functions only remove leading and
trailing whitespaces from the given string:
LTRIM(' MySQL')
= MySQL
|
ASCII |
Takes a single character and returns its numeric ASCII code: ASCII('A') = 65
|
Yes | ASCII |
ASCII('A') = 65 |
CHR |
Returns the ASCII code value, which is a numeric value from 0 through 225,
to a character:CHR(65) = A
|
Requires a different function name | CHAR |
Cloud SQL for MySQL uses the CHAR function for the same
functionality, so you need to change the function name:CHAR(65) = A
|
LENGTH |
Returns the length of a given string:LENGTH('MySQL') = 5
|
Yes | LENGTH |
LENGTH('MySQL') = 5 |
REGEXP_REPLACE |
Searches a string for a regular expression pattern: REGEXP_REPLACE('John', '[hn].', 'e') = Joe
|
No | N/A | Supported as of MySQL version 8. As a workaround, use the
REPLACE function if possible or move the logic to the
application layer. |
REGEXP_SUBSTR |
Extends the functionality of the SUBSTR function by searching
a string for a regular expression pattern:
REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
=
https://console.cloud.google.com/
|
No | N/A | Supported as of MySQL version 8. As a workaround, use the
SUBSTR function if possible or move the logic to the
application layer |
REGEXP_COUNT |
Returns the number of times a pattern occurs in a source string | No | N/A | No equivalent function available for Cloud SQL for MySQL. Move this logic to the application layer. |
REGEXP_INSTR |
Search a string position (index) for a regular expression pattern | No | N/A | Supported as of MySQL version 8. If on an older version, move this logic to the application layer. |
REVERSE |
Returns the reversed string for a given string:
REVERSE('MySQL')
= LQSyM
|
Yes | REVERSE |
REVERSE('MySQL')
= LQSyM
|
The following table describes where Oracle and Cloud SQL for MySQL numeric functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
ABS |
Returns the absolute value of a given number: ABS(-4.6) = 4.6 |
Yes | ABS |
ABS(-4.6) = 4.6 |
CEIL |
Returns the smallest integer that is greater than or equal to the given
number: CEIL(21.4) = 22 |
Yes | CEIL |
CEIL(21.4) = 22 |
FLOOR |
Returns the largest integer equal to or less than the given number: FLOOR(-23.7) = -24
|
Yes | FLOOR |
FLOOR(-23.7) = -24 |
MOD |
Returns the remainder of m divided by
n:MOD(10, 3) = 1
|
Yes | MOD |
MOD(10, 3) = 1 |
ROUND |
Returns n rounded to integer places to the right of
the decimal point:ROUND(1.39, 1) = 1.4 |
Yes | ROUND |
ROUND(1.39, 1) = 1.4 |
TRUNC(number) |
Returns n1 truncated to n2
decimal places. The second parameter is optional.
TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
|
Requires a different function name | TRUNCATE(number) |
Cloud SQL for MySQL function has a different name and the second
parameter is mandatory. TRUNCATE(99.999, 0) = 99
|
The following table describes where Oracle and Cloud SQL for MySQL
datetime functions are equivalent by name and functionality, and where a
conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
SYSDATE |
Returns the current date and time set for the operating system on which the
database server resides:
SELECT SYSDATE
FROM DUAL;
= 31-JUL-2019
|
Yes | SYSDATE() |
Cloud SQL for MySQL
SELECT SYSDATE()
FROM DUAL;
= 2019-01-31 10:01:01.0
You can change the |
SYSTIMESTAMP |
Returns the system date, including fractional seconds and time zone:
SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00
|
Requires a different function name |
CURRENT_
TIMESTAMP
|
Cloud SQL for MySQL function returns a different datetime
format by default. To reformat the output, use the
DATE_FORMAT() function.
SELECT CURRENT_TIMESTAMP
FROM DUAL;
= 2019-01-31 06:55:07
|
LOCAL_
TIMESTAMP
|
Returns the current date and time as a TIMESTAMP type:
SELECT LOCALTIMESTAMP
FROM DUAL
= 01-JAN-19 10.01.10.123456 PM
|
Returns a different datetime format |
LOCAL_
TIMESTAMP
|
Cloud SQL for MySQL function returns a different datetime
format than the default format for Oracle. To reformat the output, use the
DATE_FORMAT() function.
SELECT LOCAL_TIMESTAMP
FROM DUAL
= 2019-01-01 10:01:01.0
|
CURRENT_DATE |
Returns the current date:
SELECT CURRENT_DATE
FROM DUAL
= 31-JAN-19
|
Returns a different datetime format |
CURRENT_
DATE
|
Cloud SQL for MySQL function returns a different datetime
format. To reformat the output, use the DATE_FORMAT()
function.
SELECT CURRENT_DATE
FROM DUAL
= 2019-01-31
|
CURRENT_
TIMESTAMP
|
Returns the current date and time:
SELECT CURRENT_TIMESTAMP
FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00
|
Returns a different datetime format |
CURRENT_
TIMESTAMP
|
Cloud SQL for MySQL function returns a different datetime
format. To reformat the output, use the DATE_FORMAT()
function.
SELECT CURRENT_TIMESTAMP
FROM DUAL
= 2019-01-31 06:55:07
|
ADD_MONTHS |
Returns the date plus integer months:
ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19
|
Requires a different function name | ADDDATE |
Cloud SQL for MySQL function returns a different datetime
format. To reformat the output, use the DATE_FORMAT()
function.
ADDDATE(SYSDATE(), 1)
= 2019-08-01 06:42:49.0
|
EXTRACT (date part) |
Returns the value of a datetime field based on an interval
expression:
EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
|
Yes | EXTRACT (date part) |
EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
|
LAST_DAY |
Returns the last day of the month for a given date:
LAST_DAY('01-JAN-2019')
= 31-JAN-19
|
Partially | LAST_DAY |
Cloud SQL for MySQL function returns a different datetime
format than default format for Oracle. To reformat the output, use the
DATE_FORMAT() function.
LAST_DAY('2019-01-01')
= 2019-01-31
|
MONTH_
BETWEEN
|
Returns the number of months between the given dates
date1 and date2:
MONTHS_BETWEEN(
SYSDATE, SYSDATE-60)
= 1.96
|
Partially |
PERIOD_DIFF
|
Cloud SQL for MySQL PERIOD_DIFF function returns the
difference in months as an integer number between two periods (formatted as
YYMM or YYYYMM):
PERIOD_DIFF(
'201903', '201901')
= 2
|
TO_CHAR (Datetime) |
Converts a number, datetime, or timestamp type to a
string type
TO_CHAR(
SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01
|
Requires a different function name | DATE_FORMAT |
Cloud SQL for MySQL DATE_FORMAT function formats a date
value according to a format string:
DATE_FORMAT(
SYSDATE(),'%d-%m-%Y %H:%i:%s')
01-01-2019 10:01:01
|
The following table describes where Oracle and Cloud SQL for MySQL encoding and decoding functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
DECODE |
Compares the expression to each search value one by one using the
functionality of an IF-THEN-ELSE statement |
No | CASE |
Use Cloud SQL for MySQL CASE statement for similar
functionality |
DUMP |
Returns a VARCHAR2 value containing the data type code, length
in bytes, and internal representation of expression |
No | N/A | Not supported |
ORA_HASH |
Computes a hash value for a given expression | No | MD5 or SHA |
Use the MD5 function for 128-bit checksums or the
SHA function for 160-bit checksums |
The following table describes where Oracle and Cloud SQL for MySQL conversion functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
CAST |
Converts one built-in data type or collection-typed value into another
built-in data type or collection-typed value:
CAST('1' as int) + 1
= 2
|
Partially | CAST |
Adjust depending on if an explicit or implicit conversion is required:
CAST('1' AS SIGNED) + 1
= 2
|
CONVERT |
Converts a character string from one-character set to another:
CONVERT (
'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C
|
Partially | CONVERT |
Cloud SQL for MySQL CONVERT function requires some
adjustments to the syntax and parameters:
CONVERT(
'Ä Ê Í A B C ' USING utf8)
= Ä Ê Í A B C
|
TO_CHAR (string/numeric) |
The function converts a number or date to a string:
TO_CHAR(22.73,'$99.9')
= $22.7
|
No | FORMAT |
Cloud SQL for MySQL FORMAT function converts a number to
a format like #,###,###.##, rounding it to a decimal place and
then returning the result as a string:
CONCAT('$',
FORMAT(22.73, 1))
= $22.7
|
TO_DATE |
Oracle's TO_DATE function converts a string to a date based on
a datetimecode format:
TO_DATE(
'2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019
|
Requires a different function name | STR_TO_DATE |
Cloud SQL for MySQL
STR_TO_DATE function takes a string and
returns a date based on a datetime format:
STR_TO_DATE(
'2019/01/01', '%Y/%m/%d')
= 2019-01-01
|
TO_NUMBER |
Converts expression to a value of a NUMBER data type:
TO_NUMBER('01234')
= 1234
|
Requires a different function name | CAST |
Use the Cloud SQL for MySQL CAST function to return the same
result as the Oracle TO_NUMBER function:
CAST('01234' as SIGNED)
= 1234
|
The following table describes where Oracle and Cloud SQL for MySQL
conditional SELECT functions are equivalent by name and functionality, and
where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
CASE |
The CASE statement chooses from a sequence of conditions and
runs a corresponding statement with the following syntax:
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
|
Yes | CASE |
In addition to the CASE function, Cloud SQL for MySQL
supports the use of IF/ELSE
conditional handling inside the SELECT statement:
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
|
The following table describes where Oracle and Cloud SQL for MySQL null functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
COALESCE |
Returns the first non-null expression in the expression list:
COALESCE(
null, '1', 'a')
= a
|
Yes | COALESCE |
COALESCE(
null, '1', 'a')
= 1
|
NULLIF |
Performs a comparison between expression1 and
expression2. If they are
equal, the function returns null.
If they are not equal, the function returns
expression1:
NULLIF('1', '2')
= a
|
Yes | NULLIF |
NULLIF('1', '2')
= a
|
NVL |
Replaces a null value with a string in the results of a query:
NVL(null, 'a')
= a
|
No | IFNULL |
IFNULL(null, 'a')
= a
|
NVL2 |
Determines the value returned by a query based on whether an expression is null or not null | No | CASE |
The CASE statement
chooses from a sequence of conditions and runs a corresponding
statement:
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
|
The following table describes where Oracle and Cloud SQL for MySQL environment and identifier functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
SYS_GUID |
Generates and returns a globally unique identifier (RAW value)
made up of 16 bytes:
SELECT SYS_GUID()
FROM DUAL
=
8EFA4A31468B4C6DE05011AC0200009E
|
No | REPLACE and UUID |
As a workaround, use the REPLACE and
UUID functions to simulate the SYS_GUID
function:
REPLACE(
UUID(), '-', '')
|
UID |
Returns an integer that uniquely identifies the session user (the user who
is logged on):
SELECT UID FROM DUAL
= 43
|
No | N/A | N/A |
USER |
Returns the username of the user who is connected to the current
session:
SELECT USER FROM DUAL
= username
|
Yes | USER + INSTR + SUBSTR |
The Cloud SQL for MySQL USER function returns the
username and host name (root@IP_ADDRESS) for the
connection. To retrieve only the username, use the additional supporting
functions:
SELECT
SUBSTR(USER(), 1, INSTR(USER(), '@') -1) FROM DUAL
= root
|
USERENV |
Returns information about the current Oracle session, such as the language
of the session:
SELECT USERENV('LANGUAGE')
FROM DUAL
= ENGLISH_AMERICA.
AL32UTF8
|
No |
SHOW SESSION
VARIABLES
|
Cloud SQL for MySQL SHOW SESSION VARIABLES statement
returns the settings for the current session:
SHOW SESSION VARIABLES LIKE '%collation%';
= utf8_general_ci
|
ROWID |
Oracle assigns each row of a table a unique ROWID to identify
the row in the table. The ROWID is the address of the row
that contains the data object number, the data block of the row, the row
position, and the data file. |
Partially | N/A |
ROW_NUMBER() is available starting in MySQL
8.0. If you're using an earlier version, emulate the same functionality by using
a session variable @row_number. |
ROWNUM |
Returns a number that represents the order that a row is returned by an Oracle table | Partially | N/A | ROW_NUMBER() is available starting in MySQL 8.0. If you're
using an earlier version, emulate the same functionality by using a
session variable @row_number. |
The following table describes where Oracle and Cloud SQL for MySQL aggregate (group) functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
AVG |
Returns the average value of a column or expression | Yes | AVG |
Equivalent to Oracle |
COUNT
|
Returns the number of rows returned by a query | Yes | COUNT |
Equivalent to Oracle |
COUNT
(DISTINCT)
|
Returns the number of unique values in the column or expression | Yes |
COUNT
(DISTINCT)
|
Equivalent to Oracle |
MAX |
Returns the maximum value of a column or expression | Yes | Equivalent to Oracle | |
MIN |
Returns the minimum value of a column or expression | Yes | MIN |
Equivalent to Oracle |
SUM |
Returns the sum of a value of a column or expression | Yes | SUM |
Equivalent to Oracle |
LISTAGG |
Orders the data within each group specified in the ORDER BY
clause and concatenates the values of the measure column:
SELECT LISTAGG(
DEPARTMENT_NAME, ', ')
WITHIN GROUP
(ORDER BY DEPARTMENT_NAME) DEPT
FROM DEPARTMENTS;
-- Single line results
= Accounting, Administration, Benefits, Construction
|
Requires a different function name and syntax |
GROUP_
CONCAT
|
Use Cloud SQL for MySQL GROUP_CONCAT function to return
equivalent results:
SELECT GROUP_CONCAT(
DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME SEPARATOR ', ') DEPT
FROM DEPARTMENTS;
-- Single line results
= Accounting, Administration, Benefits, Construction
|
The following table describes where Oracle and Cloud SQL for MySQL FETCH
function is equivalent by name and functionality.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function | Cloud SQL for MySQL implementation |
|---|---|---|---|---|
FETCH |
Retrieves a specified number of rows from the result set of a multi-row
query:
SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;
|
Yes | LIMIT |
Use the MySQL LIMIT
clause to retrieve rows from a query:
SELECT * FROM
EMPLOYEES
LIMIT 10;
|
Basic filtering, operators, and subqueries
Basic filtering, operator functions, and subqueries are relatively straightforward to convert, with nominal effort required. Most of the effort revolves around converting date formats because Oracle and Cloud SQL for MySQL use different default date formats:
- The Oracle
SYSDATEfunction returns this format by default:01-AUG-19. - The Cloud SQL for MySQL
SYSDATE()function returns this format by default:2019-08-01 12:04:05.
To set date and time formats, use the MySQL
DATE_FORMAT
or the
STR_TO_DATE
functions.
The following table describes where Oracle and Cloud SQL for MySQL basic filtering, operators, and subquery functions are equivalent by name and functionality, and where a conversion is recommended.
| Oracle function | Oracle implementation | Cloud SQL for MySQL equivalent | Cloud SQL for MySQL function |
|---|---|---|---|
EXISTS/
NOT EXISTS
|
Yes |
EXISTS/
NOT EXISTS
|
SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
FROM EMPLOYEES E
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID);
|
IN/NOT IN |
Yes | IN/NOT IN |
SELECT * FROM DEPARTMENTS D
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES E);
-- OR
SELECT * FROM EMPLOYEES
WHERE (EMPLOYEE_ID, DEPARTMENT_ID)
IN((100, 90));
|
LIKE/NOT LIKE |
Yes | LIKE/NOT LIKE |
SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';
|
BETWEEN/
NOT BETWEEN
|
Yes |
BETWEEN/
NOT BETWEEN
|
SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;
|
AND/OR |
Yes | AND/OR |
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
|
SubQuery |
Yes | SubQuery |
Cloud SQL for MySQL supports subqueries in the SELECT
clause, in the JOIN clause, and for filtering in the
WHERE/AND clauses:
-- SELECT Subquery
SELECT D.DEPARTMENT_NAME,
(SELECT AVG(SALARY) AS AVG_SAL
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID =
D.DEPARTMENT_ID) AVG_SAL
FROM DEPARTMENTS D;
-- JOIN Subquery
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES E JOIN
(SELECT *
FROM DEPARTMENTS
WHERE LOCATION_ID = 2700) D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- Filtering Subquery
SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES);
|
| Operators | Yes | Operators | Cloud SQL for MySQL supports all basic operators:> | >= | < | <= | = | <> | !=
|
Best practices for Cloud SQL for MySQL queries
To maintain comparable levels of performance between Cloud SQL for MySQL and Oracle, you might need to optimize your queries. These optimizations include changing the index structures and adjusting the database schema. This section provides a few guidelines to help you achieve comparable query performance on Cloud SQL for MySQL.
Create a clustered index
When using the InnoDB storage engine, a best practice is to define a table with a primary key, because this key creates a clustered index on that table. In addition to improving query performance, this approach also lets you create additional secondary indexes. You want to avoid creating too many indexes, though. Having redundant indexes does not improve performance and can slow down DML execution. This best practice leads to a second best practice: regularly monitor for redundant indexes, and if you have any redundant ones, drop them from the database.
Use the following query to identify tables with no primary keys so that you can create primary keys for them:
mysql> SELECT t.table_schema, t.table_name
FROM information_schema.tables t LEFT JOIN
information_schema.statistics s
ON t.table_schema=s.table_schema AND t.table_name=s.table_name
AND s.non_unique=0
WHERE s.table_name IS NULL
AND t.table_schema NOT IN('sys', 'information_schema', 'mysql',
'performance_schema')
AND t.`TABLE_TYPE` <> 'VIEW';
Use the following query to find tables which have no indexes so you can create indexes for them:
mysql> SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.tables t
WHERE table_name NOT IN
(SELECT table_name FROM (
SELECT table_name, index_name
FROM information_schema.statistics
GROUP BY table_name, index_name) tab_ind_cols
GROUP BY table_name)
AND table_schema NOT IN('sys', 'information_schema', 'mysql', 'performance_schema')
AND TABLE_TYPE <> 'VIEW';
Use the following query to check for redundant indexes so that you can remove the redundancies:
mysql> SELECT * FROM sys.schema_redundant_indexes;
Adjust query parameters
To tune query performance, you might need to adjust session parameters. Cloud SQL for MySQL has a set of flags that you can alter for this purpose, including the following flags:
- InnoDB-related parameters
SORTparametersJOINparameters- Cache-handling parameters
Monitor queries
Slow-running queries can cause the system to stop responding or lead to other bottlenecks, so it's important to regularly monitor queries.
There are several ways to diagnose slow-running SQL statements:
- Use the Cloud SQL for MySQL dashboard for real-time and historical insights on slow-running queries.
- Use Cloud Monitoring to monitor the Cloud SQL for MySQL slow query log.
Use the Cloud SQL for MySQL
statement_analysisview to see the runtime statistics about a SQL statement:mysql> SELECT * FROM sys.statement_analysis;
Analyze Cloud SQL for MySQL queries
The query optimizer in Cloud SQL for MySQL generates an execution plan for
SELECT, INSERT, UPDATE, and DELETE statements. These plans are useful
when you adjust a slow-running query. There are a few considerations to keep in
mind:
- Execution plans are not database objects that need to be migrated; rather, they are a tool for analyzing performance differences between Oracle and Cloud SQL for MySQL running the same statement on identical datasets.
- Cloud SQL for MySQL does not support the same execution plan syntax, functionality, or output as Oracle.
Here is an example plan to illustrate the differences between an Oracle execution plan and a Cloud SQL for MySQL execution plan:
SQL> EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
mysql> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | EMPLOYEES | NULL | const | PRIMARY | PRIMARY | 3 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
Optimize stored procedures and triggers
In contrast to Oracle, Cloud SQL for MySQL stored procedures and
functions are parsed at each execution. A helpful tool for benchmarking stored
procedure and function performance is the
MySQL BENCHMARK() utility.
This tool takes two parameters, an iteration count and an expression, and
estimates the runtime of the given expression (for example, stored procedure,
function, and SELECT statement). The output represents the approximate total
runtime over all iterations.
The following is an example to illustrate the BENCHMARK() utility:
-- SELECT Expression Example
mysql> select benchmark(10000000, 'select sysdate()');
+-----------------------------------------+
| benchmark(10000000, 'select sysdate()') |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.12 sec)
-- Result: Run time of 0.12 sec for 1,0000,000 iterations
-- FUNCTION Example
mysql> select benchmark(1000000, func1());
+-----------------------------+
| benchmark(1000000, func1()) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (2.54 sec)
-- Result: Run time of 2.54 sec for 1,000,000 iterations
If you notice a performance regression during the conversion, use the
MySQL EXPLAIN command
to identify possible factors contributing to the regression. One common
solution for slow performance is to alter a table index structure to accommodate
the MySQL optimizer. Another common practice is to optimize a converted PL/SQL
code by reducing unnecessary data retrieval or by using temporary tables within
the procedural MySQL code.
What's next
- Explore more about MySQL user accounts.
- Explore reference architectures, diagrams, and best practices about Google Cloud. Take a look at our Cloud Architecture Center.