MySQL References
The online manual for MySQL is not great, I find the book by DuBois (see below)
to be more useful.
Online manual: http://www.mysql.com/doc/
or http://mysql.he.net/doc/
SQL Tips
 | When checking for an empty or null column in a where clause, use
column_name <=> '' or column_name is null
This will match if the column contains an empty string or Null. |
phpMyAdmin Tips
You may see many databases listed in phpMyAdmin, you only have access to the
database with the same name as your account. Other tips:
| Do not use table, index, class, or other SQL commands as table names or
column names. |
| If you get an error: MySQL said: No Database Selected, be sure
that you have selected your own database. |
| Things to check if you get an error when inserting new rows (or the row is
not added):
 | VARCHAR type without a size argument. |
| TEXT or BLOB types (any variety) with a size argument. |
| ENUM type, the Length/Set values should be in single quotes and
separated by commas; the Default value is not entered in quotes. |
|
| If you get a message like: "Error 'BLOB column 'sname' used in key specification without a key length",
you are trying to create a key that refers to some type of TEXT or BLOB
column. Use a SQL command that specifies how many characters of the column
should be used for the key. For example:
| CREATE INDEX part_of_name ON customer (name(10)) |
| ALTER TABLE `s` ADD INDEX(sname(10)) |
|
To load Excel data into a MySQL database using phpMyAdmin:
| Prepare a comma separate list of columns to be loaded in the same sequence
as the data values in your worksheet. |
| In Excel, select File, SaveAs, and select the Comma Separated Values
format (.csv). Store the file on your local drive. |
| Open the .csv file in a text editor (like UltraEdit) and remove any extra
rows or columns (commas) and save the file. Each line should contain the same number of commas
that are not enclosed in quotes. |
| In phpMyAdmin select the database and table where you want to load the
data. You may want to Empty the table to delete old data before doing
the load. |
| View the table Properties page and click the Insert textfiles into
table link. Specify the following options on the next page:
| Location of the text file, browse and specify the filename on
your computer. In Netscape, you may have to specify Files of type:
all files (default is HTML files). |
| Replace table data with file: check this box,
your data will replace any matching records that are already in the table. |
| Fields terminated by: change the ';' to a comma ','. |
| Fields enclosed by: leave the double quote and select optionally.
If any of your data includes a comma, enclose the entire field in double
quotes. |
| Fields escaped by: leave \ in this field. |
| Lines terminated by: leave "\r\n", shorthand for the carriage return and line feed characters that
Windows inserts at the end of every line. For Macintosh, try saving the
file in CSV for DOS format or specify \r for carriage return only (I
have not tested on a Mac). |
| Column names: enter the comma separated list of the names of
the columns you want to load, if you are not loading all columns in same
sequence as your table definition. You may only be able to see part of your
list. |
| Click Submit and you are on your way. Data containing
apostrophe, like "O'Reilly" seems to be handled correctly. |
|
| Browse the table and check your data. |
Differences Between Oracle and MySQL
While Oracle and MySQL are both relational databases, there are many
differences large and small between them (in addition to price):
| Table and column names are not case sensitive in Oracle, they are in
MySQL. |
| MySQL allows changes to a column definition and will try to convert the
data. In Oracle a similar change requires dropping the column and data. |
| Data type differences:
| Text data items: in Oracle a VARCHAR may be ~4000 bytes, in MySQL the
limit is 255. Long text items in Oracle are BLOBS, in MySQL they are
TEXT, these types work differently. |
| Date data type: Oracle data includes time, MySQL has separate date,
time, and datetime types. Date manipulation routines are different. |
| Numbers: Oracle has one numeric type, 'number' that holds up to 38
digits. MySQL has many different numeric types including int, decimal,
and float but not number. |
| ENUM (enumerated) and SET data types are supported by MySQL and not
Oracle. |
|
| Automatic numbering: Oracle sequences are separate data items in a
database and used to assign values to columns. In MySQL auto_increment is an
attribute of a column and there may be only one auto_increment column per
table. |
| Text lookup in Oracle is typically case sensitive. In MySQL the default
lookup for text is not case sensitive. |
| Restart recovery, record level locking, transaction boundaries,
transaction logging, and transaction integrity protection: Oracle provides
strong support; MySQL provides very limited support. |
| Referential integrity, views, queries with sub-selects, stored procedures,
triggers, replication, etc. are supported by Oracle and not MySQL. |
| The meaning of a database versus a tablespace and the Grant facilities for
sharing information are different. |
With careful planning, it is possible to develop an application using MySQL
(which is much faster than developing for Oracle) and then converting to Oracle
when the application is stable, the service has developed enough traffic, and
funding allows the change.
For More Information
For more information on MySQL, see:
For tools to migrate from MySQL to Oracle see http://technet.oracle.com/tech/migration/content.html |
