3 things MySQL over Oracle and Sybase

I did web application by using MySQL, Oracle, Sybase in various projects, and concluded that MySQL's 3 benefits over Oracle and Sybase.

1. For insert, MySQL's insert ignore is very useful for a auto-increment insert processing.

Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
e.g, I used 'insert ignore' in Perl +DBD::MySQL env like this:

1. BEGIN
2. select count(*) into v_count from table where name = m1;
3. IF v_count == 0 THEN
4. INSERT into TABLE values(name, ...,sysdate);
5. END IF;
6. COMMIT;
7. EXCEPTION
8. WHEN OTHERS THEN
9. RETURN 'Error: NULL';
10. ROLLBACK;
11. END;

Sybase doesn't have "ignore" option either, so with the same way Oracle does. Here you can see MySQL makes auto-increment insert easier.

2. Smart conversion.
MySQL can convert data type, or cut down longer part to fix the size.
e.g, a field: name varchar(20) not null, the actual length is 30: strlen($name) = 30;
MySQL won't throw out a exception to make process terminate, it does magic work to make the insert successfully:
(1) cut down redundant chars, make strlen($name)=20;
(2) then insert this record.

MySQL silently and seamless cuts the excess string, to make the insert successfully.
When insert, Oracle or Sybase will throw a exception, and stop the process.
It will make debug very difficult if multi-inserts or long insert columns.
To capture the error with distinct message, in a Sybase, T-SQL env, the corresponding syntax is:

1. BEGIN TRY
2. { sql_statement | statement_block }
3. END TRY
4. BEGIN CATCH
5. [ { sql_statement | statement_block } ]
6. END CATCH
7. [ ; ]

So MySQL uses better choice over Oracle and Sybase: it smartly and intelligent process the case without manually interaction.

3. limit: Limit the number of records returned. MySQL's limit is super, it retrieve the data in a given range.

• SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
• With one argument, the value specifies the number of rows to return from the beginning of the result set:
• SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

      select *
      from ( select a.*, rownum rnum
      from ( YOUR_QUERY_GOES_HERE ) a
      where rownum = 30;

Sybase and MS SQL are sibling, they use almost the same syntax. They don't have limit either, so even harder to do the same case. To control it, I use Perl's Tie::Hash module to store and bind database data to temporarily memory instead of mysql's limit function. Sybase has excellent functionality of store procedure, and cursor, but not good at limit query.