The problems Oracle->mysql encountered

1, Case sensitive differences (if the server OS is Linux).
In Oracle, it is generally not case-sensitive. Sometimes when we use Oracle to pay no attention to case, table and field names are case insensitive, like this: the INSERT into tableName and insert into tableName effects are the same, with tools to export the Create/data initialization script, The resulting general table name and field name are converted to uppercase.
In MySQL, however, the case sensitivity of the operating system used determines the case sensitivity of the database name and table name. The database corresponds to a directory in the data directory, and each table in the database corresponds to at least one file in the database directory (or possibly multiple, depending on the storage engine). Therefore, using a database or table is actually manipulating these files (folders), so the case sensitivity of using the operating system determines the case sensitivity of the database name and table name. is case-sensitive in Linux-kernel-based operating systems.
workaround : Keep the MySQL database name consistent with the case of Oracle, the table name is consistent with the table name in the SQL string in the application, and if the field names in your application use double quotes, match the case of the field names in SQL with the characters in the double quotes. If your application references table names, fields that are not uniformly case-sensitive, then the problem is big.

2. The difference between reserved words
The function name of the SQL language (for example: inteval,show) is reserved word. The reserved words in Oracle are available as table names and field names, and do not affect use, but the reserved words in MySQL are not available as table names and field names if used to report syntax errors.
workaround : Use the ' ' ' symbol for the reserved word in the SQL statement, which is located above the TAB key of the keyboard, and if the field name has another method tablename. Field name. Like this: INSERT INTO tablename (ID, ' interval ') value (... or INSERT INTO TableName (ID, tablename.inteval) value (...).

3. Differences in data types
In MySQL, there is no varchar2, number,mysql, and numeric in Oracle, and of course there is no MySQL time type in Oracle.
The workaround is to replace

4, the difference of automatic growth type
Oracle has Sequence,mysql, but has auto_increment properties.
Workaround: convert sequence in Oracle to use the Auto_increment attribute, there may be a way to solve the problem in some cases, and create a separate table to specifically record the autogrow data.

5, the difference between the index length limit

Starting with MySQL 4.1.2, the MyISAM and InnoDB table index lengths support 1000 bytes, that is, the length of the indexed field cannot exceed 1000 bytes, if more than the error is reported: ERROR 1071 (42000): Specified Key was too Long Max key length is bytes. If it is UTF-8 encoded, it is equivalent to the length of 333 characters (because UTF8 one character occupies 3 bytes). Oracle has a much looser index length limit than MySQL.
Workaround: You don't have to say more, either change the definition of the index, or change the definition length of the field.

Second, for database compatibility, we should pay attention to what.
Database compatibility should be a concern for database design, because sometimes customers have a database that is already in use, and do not want to maintain two databases at the same time, so compatible with a variety of databases can also become a selling point of the product.
The key to database compatibility is compliance with standard usage.
1, abide by the standard usage, try not to use a database-specific usage.
such as the use of msyql ' symbols, for example, a lot of people have this usage, when using Oracle development to create sequence, before inserting data into the table first select Seq.nextval from DUAL, and then the value of the query as value into the table , this usage can not adapt to the database without sequence, each database has the use of auto-growth, if need to use it should be used completely. For example, different databases have extended paging queries, and PostgreSQL has offset,limit,oracle.

2. Avoid database case-sensitive issues

Select whether the database table name and field name are in uppercase or lowercase, and are fully unified during database design and coding

3. Reserved words

Requires that the database Designer try not to use reserved words for table names and field names. There are also many people who have this usage, adding ' _ ' to the table name and field name, like this: Create TABLE _tablename (_id integer). This will never cause problems with reserved words

The problems Oracle->mysql encountered