Category: MySql

Help and howto do things in MySQL

MySQL is the world’s most popular open source database, recognized for its speed and reliability, and is frequently used with PHP to create websites. The Electric Toolbox website uses MySQL as its database backend, and I have personally been using MySQL since 1999. In that time it has proven itself to me to be an extremely fast and reliable database management system, although the earlier versions lacked some features found in other database systems, such as stored procedures, triggers, sub selects and joined table updates. Most of these are available from the 4.1 and 5.0 releases.

If you are looking for MySQL databases – for example geographical databases, I suggest you take a look at SQL Dumpster

MySQL Query Browser “The memory load of the system is extremely high” error

The MySQL Query Browser is a GUI tool for running queries on MySQL database servers. This post looks at an issue where the query browser reports an issue with memory load being extremely high and prevents the query from running. There are versions of the MySQL Query Browser for Windows, OS/X and Linux (or you can compile from source); I got this error on the Windows version and it may or may not affect the other platforms.

Read More »

MySQL: SQL to drop a column from a table

It’s probably more common for people to edit MySQL tables using a more visual tool such as phpMyAdmin but it is possible to use a manually written SQL query instead to modify tables (this is what a tool like phpMyAdmin does in the background). This post shows how to drop a column from a MySQL table with a SQL query.

Read More »

Use mysqldump to get the schema only

The MySQL command line tool mysqldump is used to create backup copies (or dumps) of databases including the structure or schema and the data itself. There are a number of command line flags which can get MySQL to dump just the data or just the structure instead of everything. This post looks at how to dump just the schema of the MySQL database as a whole, for a single table, or for several tables.

Read More »

Get MySQL status information from the command line

This post shows how to get some MySQL status information from the command line on a Linux/Unix machine using either the mysqladmin command or the mysql init script. Information returned includes the version, connection type, socket file location, uptime and some other stats.

Read More »

MySQL CONVERT_TZ returns null

MySQL’s CONVERT_TZ function converts datetime values from one timezone to another. If the timezones haven’t been set up in the MySQL database then the value returned from the function will be null. This post shows how to fix the problem.

Read More »

Drop multiple MySQL tables

Last week I looked at how to drop a table with MySQL showing how to do this with a query and then using the phpMyAdmin web based interface. This time I will show how to drop multiple tables with a single query and then how to do the same with phpMyAdmin.

Read More »

Cross Table Delete with MySQL

Deleting records with MySQL can be done by referencing records in another table with a cross join, allowing data to be deleted from one table based on values in another, or where there are no associated records in the other table.

Read More »

Get a MySQL table structure with DESCRIBE

There are at least two ways to get a MySQL table’s structure using SQL queries. The first is using DESCRIBE and the second by querying the INFORMATION_SCHEMA. This post deals with the DESCRIBE function and next week I’ll look at the latter.

Read More »