MySQL and Oracle Conversion Problems and Solutions

MySQL and Oracle conversion problem set:

1. mySQL does not support sequences. However, MySQL uses an auto-increment mechanism, and uses auto-increase in the ID column to replace sequences, when inserting SQL statements, enter Null in the position corresponding to the ID column. However, the problem occurs when the file is uploaded and the file name is the ID value, because there is no sequence, how can I retrieve the ID value? The file upload mechanism without the ID value must be changed accordingly.

Solution 1: select max (id) + 1 in the original table to obtain the future id value.

2. MySQL does not use common functions or methods in Oracle, such as the system time sysdate. I do not know whether the getdate () method exists in MySQL.

2.1 For example, in MySQL, set the default value of the time and date column to system date.

Test result 1: MySQL does not have a virtual table dual, sysdate, or the getdate () method supported by other databases.

Solution 1:

The methods to replace sysdate in MySQL are sysdate (), curdate (), curtime (), and now.

Sysdate ():

Curdate (): extract the current system date, in the format of 'yyyy-mm-dd '. Use curdate + 0 output format, such as 'yyyymmdd '.

Curtime (): retrieves the current system time, in the format of 'hh: mi: ss '. Use curtime () + 0 output format such as 'hhmis '.

Now (): Same as sysdate (), the system date plus time is taken out, in the format of 'yyyy-mm-dd hh: mi: ss '. Use now () + 0 to output a date time without a separator.

Second, method functions are not supported when setting the Default attribute in MySQL, but a new type of timestamp is provided to replace date. This type refers to date or datetime with the default value of system time date. A value of the timestamp type can be truncated. For example, timestamp (14) indicates the output to the second, timestamp (6) indicates 'yy-mm-dd', and so on.

Finally, the perfect solution is to set the type of the date column to timestamp (8.

3. MySQL does not support simplified JOIN table writing. You must strictly use the join on statement.