Book HomeMySQL and mSQLSearch this book

6.8. Extended Functionality

Both MySQL and mSQL have a few quirky extensions that do not really have counterparts in the other database engine. Most of MySQL's extensions are generally in line with the ANSI SQL standard. mSQL's extensions are simply related to special variables you can access while working with an mSQL database.

6.8.1. MySQL Features

MySQL goes well beyond mSQL's support for SQL by supporting functions and a limited concept of outer joins. Functions in SQL are similar to functions in other programming languages like C and Perl. The function takes zero or more arguments and returns some value. For example, the function SQRT(16) returns 4. Within a MySQL SELECT statement, functions may be used in either of two places:

As a value to be retrieved

This form involves a function in the place of a column in the list of columns to be retrieved. The return value of the function, evaluated for each selected row, is part of the returned result set as if it were a column in the database. For example:

# Select the name of each event as well as the date of the event
# formatted in a human-readable form for all events more
# recent than the given time. The FROM_UnixTIME() function
# transforms a standard Unix time value into a human
# readable form.
SELECT name, FROM_UnixTIME(date) 
FROM events
WHERE time > 90534323

# Select the title of a paper, the full text of the paper,
# and the length (in bytes) of the full text for all 
# papers authored by Stacie Sheldon.
# The LENGTH() function returns the character length of
# a given string.
SELECT title, text, LENGTH(text)
FROM papers
WHERE author = 'Stacie Sheldon'
As part of a WHERE clause

This form involves a function in the place of a constant when evaluating a WHERE clause. The value of the function is used for comparison for each row of the table. For example:

# Randomly select the name of an entry from a pool of 35
# entries. The RAND() function generates a random number
# between 0 and 1 (multiplied by 34 to make it between 0 
# and 34 and incremented by 1 to make it between 1 and
# 35). The ROUND() function returns the given number 
# rounded to the nearest integer, resulting in a whole
# number between 1 and 35, which should match one of
# the ID numbers in the table.
SELECT name 
FROM entries 
WHERE id = ROUND( (RAND()*34) + 1 )

# You may use functions in both the value list and the
# WHERE clause. This example selects the name and date
# of each event less than a day old. The UNIX_TIMESTAMP()
# function, with no arguments, returns the current time 
# in Unix format.
SELECT name, FROM_UnixTIME(date)
FROM events
WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24) )

# You may also use the value of a table field within
# a function. This example returns the name of anyone 
# who used their name as their password. The ENCRYPT()
# function returns a Unix password-style encryption
# of the given string using the supplied 2-character salt.
# The LEFT() function returns the left-most n characters 
# of the given string.
SELECT name
FROM people
WHERE password = ENCRYPT(name, LEFT(name, 2))

Finally, MySQL supports a more powerful joining than the simple inner joins we have used so far. Specifically, MySQL supports something called a left outer join (also known as simply outer join). This type of join is similar to an inner join, except that it includes data in the first column named that does not match any in the second column. If you remember our author and book tables from earlier in the chapter, you will remember that our join would not list any authors who did not have a book in our database. It is common that you may want to show entries from one table that have no corresponding data in the table to which you are joining. That is where an outer join comes into play:

SELECT book.title, author.name 
FROM author
LEFT JOIN book ON book.author = author.id

Note that a outer join uses the keyword ON instead of WHERE. The results of our query would look like this:

+----------------+----------------+
| book.title     | author.name    |
+----------------+----------------+
| The Green Mile | Stephen King   |
| Guards, Guards!| Terry Pratchett|
| Imzadi         | Peter David    |
| Gold           | Isaac Asimov   |
| Howling Mad    | Peter David    |
| NULL           | Neil Gaiman    |
+----------------+----------------+

MySQL takes this concept one step further through the use of a natural outer join. A natural outer join will combine the rows from two tables where the two tables have identical column names with identical types and the values in those columns are identical:

SELECT my_prod.name 
FROM my_prod
NATURAL LEFT JOIN their_prod

6.8.2. mSQL Features

mSQL has five "system variables" that you can include in any query. We have already covered one of those values, _seq. The others are:

_rowid

A unique identifier corresponding to a row of data being returned. You can use it with subsequent UPDATE or DELETE statements to improve efficiency. This approach, however, is definitely not recommended because multiple clients can mess each other up. Specifically, two clients can select the same row. The first one deletes it and then a third client inserts a new row. The new row can have the same _rowid value as the deleted row. If the second client then tries to update or delete using that _rowid, it will affect data it did not intend to affect.

_timestamp

The time when the row in question was last modified. Under the current version of mSQL, this value is in the standard Unix time format. This behavior may change in future versions, so you should only use this value to compare with timestamps in other rows.

_sysdate

Returns the current time on the mSQL server. This time can be used to ensure that all times used in the database are synchronized even if the clients are on systems with varying times. This time is given in the standard Unix format and will be the same no matter which table you select it from.

_user

This value holds the name of the user for the current client connection. As with _sysdate, this value is not dependent on the table from which you choose to select it.



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.

This HTML Help has been published using the chm2web software.