Segmentation Fault: Core dumped..;-)

MySQL vs Oracle: Documenting observations/nuggets as I dive into MySQL

Posted : May 19, 2004 at 12:08 pm [America/Los_Angeles]

No, I am not here to bash Oracle or MySQL or both. Sorry, biling is almost an art that few possess. I ain’t one of them.

So, here’s the deal. I have worked with Oracle’s flavor of SQL for most of my professional life so far. During this time, MySQL has gotten my attention more so during installation of open-source softwares (like Movable Type) than as my application’s RDBMS backend. It all changed yesterday as I literally had to dive into MySQL for my current project

Here are some of my observations/notes:

1. In Oracle, you use ‘SQLPlus’ if you want to run queries. In MySQL, it’s ‘mysql’. If you like using GUI-based tools to do your SQL, in Oracle, you may be using Quest’s TOAD, TOra or the many commercial products out there. In the case of MySQL, I tend to flip-flop between ‘mysql’ client, TOAD for MySQL or MySQL Control Center (MySQLCC).

2. In Oracle, there is a concept of “DUAL” table (it’s really not a table) which is used too often to do things like:

SQL> SELECT 1+1 FROM DUAL;
       1+1
----------
         2
SQL> SELECT SQRT(4) FROM DUAL;

   SQRT(4)
----------
         2
SQL>

In MySQL, you don’t need the “FROM DUAL”:

mysql> select 1+1 ;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.03 sec)
mysql> select sqrt(4);
+----------+
| sqrt(4)  |
+----------+
| 2.000000 |
+----------+
1 row in set (0.89 sec)
mysql>

3. In SQLPlus, there are a bunch of directives made available which makes it easy to spool, make SQL output look pretty, time queries, etc.

set timing on
set feed on
set echo on
set term on
set serveroutput on size 1000000
col mycurdt format A20

In MySQL, the SQL output is definitely much prettier by default which makes the life of an application developer that much simpler:

mysql> use mt;
Database changed
mysql> select * from ind_sites;
+---------+-------------+----------------------+---------------------+
| site_id | site_author | site_url             | site_created_on     |
+---------+-------------+----------------------+---------------------+
|       4 | Jivha       | http://www.jivha.com | 2004-05-18 21:55:29 |
+---------+-------------+----------------------+---------------------+
1 row in set (3.31 sec)
mysql>
mysql> select * from ind_sites G
*************************** 1. row ***************************
        site_id: 4
    site_author: Jivha
       site_url: http://www.jivha.com
site_created_on: 2004-05-18 21:55:29
1 row in set (0.03 sec)
mysql>

Notice, the \G option that was used to run the query and how it neatly arranged the columns vertically (as opposed to horizontally). I am pretty sure there is no easy way to do this in SQLPlus. However, SQLPlus is probably much more powerful thanks to the bazillion directives (as shown above) that you can use to configure it to your taste. Personally, I don’t care too much about the many directives that SQLPLus has. All I really need, and I have not yet figured it out, is how to enable spooling in ‘mysql’ client.

4. While using these command-line clients to access Oracle or MySQL is great, it sure is a pain that the prompts by default is:

SQL> (for Oracle's SQLPlus)


mysql> (for MySQL)

If you wanted to set your SQL prompt in Oracle, this site will tell you how to do it. I like the fact that you’ve an option in Oracle whereby you can have your prompt set as soon as you log in. Here’s how you can set your prompt in MySQL (in versions > 4.0.x):

% mysql
mysql> PROMPT d>_
PROMPT set to 'd>_'
(none)> USE mtdb;
Database changed
mtdb> USE test;
Database changed
test>

I have not yet found out how to make this thing be run automatically so that I would not have to type ‘PROMPT \d>\_’ everytime I log into a MySQL database. If you know, I’d be interested.

That’s all for now. I am sure I will have more later.

- Anand

Category: Services and Software