Alex Kochis, senior product manager for Microsoft's "Windows Genuine Value Added Program", wrote in a blog that human error led to the error. Alex said that the new software was accidentally loaded onto the server running WGA. As a result, the server cannot operate normally, and reasonable applications from genuine software users cannot be processed correctly. The database used by customers is MySQL, and the developed products support Oracle. In order for customers to pay for it, we must change the database environment from Oracle to MySQL. We encountered the following problems during the conversion process, and we hope to provide some reference for colleagues who have encountered the same problem. If we pay attention to the portability of the database during the initial design and coding process, there is no need to do any additional work in this case.
1. Problems encountered when the database environment changes from Oracle to MySQL.
Because the logic remains unchanged, the principle is not to change the application code, only the creation/initialization sql of the database table. Below are the problems we encountered and their solutions.
1. Case-sensitive differences (if the server OS is Linux).
Oracle is generally not case sensitive. Sometimes we don't pay attention to the case problem when using Oracle. Table names and field names are not case-sensitive without double quotes. Like this: insert into tableName and insert into TABLENAME have the same effect. Use tools to export creation/data initialization. Script, the results obtained are generally converted to uppercase table names and field names.
But in MySQL, 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 multiple, depending on the storage engine). Therefore, using a database or table is actually manipulating these files (folders), so the case sensitivity of the operating system determines the case sensitivity of the database name and table name. It is case-sensitive in operating systems with Linux as the kernel.
The solution is to keep the database name of MySQL consistent with the case of Oracle, and the table name consistent with the table name in the SQL string in the application. If the field name in the application uses double quotes, please change the field name in SQL The case of the name must be consistent with the characters enclosed in double quotes. If the table names and fields referenced by your application do not have uniform case, you will be in big trouble.
2. The difference between reserved words.
Function names in SQL language (such as inteval, show) are reserved words. Reserved words in Oracle can be used as table names and field names, and do not affect their use. However, reserved words in MySQL cannot be used as table names and field names. If used, a syntax error will be reported.
The solution is to quote the reserved words in the SQL statement with the '`' symbol, which is located above the tab key of the keyboard; if it is a field name, there is 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 and number like in oracle. Mysql has corresponding varchar and numeric. Of course, there is no mysql time type in oracle.
The solution is replacement.
4. The difference between automatic growth types.
Oracle has sequence, but mysql does not, but it has the auto_increment attribute.
The solution is to convert the sequence in Oracle to use the auto_increment attribute. In some cases, there may be a way to solve the problem. Create a new independent table to record automatic growth data.
5. The difference in index length limits.
Starting from MySQL 4.1.2, the index length of MyISAM and InnoDB tables supports 1000 bytes, which means that the length of the index field cannot exceed 1000 bytes. If it exceeds, the following error will be reported: ERROR 1071 (42000): Specified key was too long ; max key length is 1000 bytes. If it is UTF-8 encoding, it is equivalent to a length of 333 characters (because one UTF8 character occupies 3 bytes). Oracle's index length limit is much looser than MySQL's.
There is no need to elaborate on the solution, either change the definition of the index or change the definition length of the field.
2. What should we pay attention to for database compatibility?
Database compatibility should be an issue that should be paid attention to in database design, because sometimes customers have databases that are already in use and do not want to maintain two databases at the same time. In this case, compatibility with multiple databases can also become a selling point of the product.
The key to achieving database compatibility is to adhere to standard usage.
1. Follow standard usage and try not to use certain database-specific usage.
Such as the usage of msyql's '`' symbol,
For another example, many people have this usage. When using Oracle to create a sequence, SELECT seq.nextval FROM DUAL; before inserting data into the table, and then insert the value obtained from the query into the table as value. This usage does not work. It is not suitable for databases without sequence. Each database has automatic growth usage. If you need to use it, you should use it completely.
For another example, different databases have expanded paging queries. Postgresql has offset and limit, but Oracle does not.
2. Avoid database case sensitivity issues.
Choose whether database table names and field names should be in uppercase or lowercase, and be completely unified during the design and coding of the database.
3. Reserved words.
Database designers are required to try not to use reserved words for table names and field names. There are also many people who use this method, adding '_' before the table name and field name, like this: create table _tablename (_id integer). This way you never have problems caused by reserved words.