William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

3 things MySQL over Oracle and Sybase

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:

  • $sth=$dbh->do(qq{insert ignore into } . TABLE . qq{(name,…,date) values($dbh->quote($name),…,now()) });
  • Oracle and Sybase don’t have the “ignore” option, so a corresponding Oracle PL/SQL store procedure is needed to do the similar thing 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

    Oracle PL/SQL can do the same thing, but more complex and low performance:

          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.

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: