Archive for category mysql
Selecting a random row using MySQL
Posted by vinoaj in development, howto, mysql on July 25, 2007
There are many examples where you would need to retrieve a random record or more from a set of tables. For example your page template may be cycling through QOTDs, testimonials, offers, specials, and so forth.
To retrieve a random row in MySQL, use the ORDER BY RAND() statement to randomly order the rows of a table. Then use LIMIT x to retrieve the first x rows from the randomised representation of the table.
For example:
-
/* Retrieve 5 random special offers to display in your e-commerce store's sidebar */
-
SELECT offer_id
-
FROM special_offers
-
ORDER BY RAND()
-
LIMIT 5
Of course, we can make our query as complex as needed, and then randomly order the resultset.
-
SELECT so.offer_id, i.title
-
FROM special_offers so
-
LEFT JOIN items i ON (so.item_id = i.item_id)
-
LEFT JOIN item_categories ic ON (i.item_id = ic.item_id)
-
WHERE ic.category_id = 2344
-
ORDER BY RAND()
-
LIMIT 5
Using mysqldump to export a database
mysqldump is a useful utility for dumping the contents of an entire database to an SQL file.
-
mysqldump --user=dbusername --password=dbpassword db_name_here> db_name_here.sql
By default the --opt option is enabled, which is a shorthand option for specifying the below options. According to the documentation, using this option "... should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly".
-
--add-drop-table
-
--add-locks
-
--create-options
-
--disable-keys
-
--extended-insert
-
--lock-tables
-
--quick
-
--set-charset