MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to …

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column FROM table
ORDER BY RAND() 
FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM 
( SELECT column FROM table
  ORDER BY …

An introduction to the normalisation of databases that requires no prior knowledge and serves as an excuse to introduce cat content into MySQL-dump.

Continue reading "Nermalisation"

The most recent MySQL server I used in a production environment was a 3.23, so in conjunction with reading the 5.0 sources, I saw fit to reread large parts of the documentation to see where updates had happened, fixes were applied, extensions added. Here are some random clippings from the docs that illustrate quirks, changes from MySQL 3.23 (other than those noted in 3.23->4.0, 4.0->4.1, 4.1->5.0 which we'll give a brief tour of at the end) and differences from the standard. If you recently read the docs, kindly walk on, there's nothing to see here. If …

By chance, I came across this interactive interactive SQL tutorial — select your preferred engine, create queries for a variety of exercises, see the results online immediately. Instructive for the beginner, possibly fun for the advanced. : )