kwatog & co.

This one has been on my draft for more than a year already so I better fix it up and share. Besides, I find myself more and more googling my site to find my posts instead of searching via the WP admin. Without further ado, here it is..
Read more…

Here’s few of the usual equivalent Oracle data functions in MySQL.

DATE ADD (SYSDATE +/-1)

SELECT DATE_ADD(SYSDATE(),INTERVAL 1 DAY);
SELECT DATE_ADD(SYSDATE(),INTERVAL -10 DAY);

Read more…

Here’s the MySQL equivalent of Oracle’s NVL and MSSQL/Sybase’s ISNULL functions.

Syntax


IFNULL(expr1,expr2)


Usage

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. Take note that IFNULL() returns a numeric or string value, depending on the context in which it is used. I haven’t used it for other data types yet but I reckon it would have the same results. That is to return the same data type as that of expr1. Another important thing to remember is that expr1 and expr2 should have the same data type

Most Oracle guys (and I heard PostgreSQL, too) will be missing sequence in MySQL. The nearest equivalent that MySQL has for this feature is the AUTO INCREMENT field. However, auto increment is sometimes not sufficient and time will come that you will be missing it.

Case in point is the alpha-numeric auto-generated code. In Oracle, it’s easy to generate AA123, AA124, AA125, etc., etc. All we have to do is concat the alpha part (AA) and with the nextval of sequence. For MySQL, the alpha part and the numeric part have to be saved in separate fields.

However, Maresa of Microshell has created an alternative solution for it. The article discusses a way to emulate the nextval function in mysql. It a nutshell, they created a table similar to the all_sequences of Oracle. This is where you insert your sequences. Then, create a function named nextval() function which increments and returns value of the sequence.
You can check the procedure here.

It’s pretty neat, actually. It’s an excellent alternative and you can even create similar functions like currval(), prevval(), among others. However, I have some reservations on his approach especially on the issue of concurrency. You see in Oracle, calling the nextval() will increment the value and that’s already persisted (saved) in the sequence table even without saving your query/dml statement. On this approach, other queries accessing the pseudo-nextval will have to wait until your query finishes. Not a good idea.

So for now, I’ll will still have to make do with whatever MySQL is providing. If there’s any consolation, MySQL provides LAST_INSERT_ID() function in case you want to get the latest id inserted. See details here here.

So I’m just hoping that MySQL will implement an equivalent feature in the near future. With the imminent takeover by Oracle, I’m actually hoping that development of MySQL will push through and take a steam. We all know that the development of this widely used open-source database engine has been hampered ever since Sun bought it.

Sequence is a little feature. But little as it seems, it solves a lot of problems and helps improve code development time.

Backup MySQL Database into a File


-bash-3.2$mysqldump -ujhondoe -p mydb_name > mydb_name_backup.sql
password:
-bash-3.2$ zip mydb_name_backup.zip mydb_name_backup.sql


Load MySQL Backup Into Database


-bash-3.2$mysql -ujhondoe mydb_name < mydb_name_backup.sql
password:


Create User in MySQL


mysql> CREATE USER 'jhondoe'@'localhost' IDENTIFIED BY 'mypassword';
mysql> GRANT ALL PRIVILEGES ON mydbname.* TO 'jhondoe'@'localhost' WITH GRANT OPTION;

mysql> CREATE USER 'jhondoe'@'%' IDENTIFIED BY 'mypassword';
mysql> GRANT ALL PRIVILEGES ON mydbname.* TO 'monty'@'%' WITH GRANT OPTION;