Save typing effort for complex MySQL queries with custom variables or procedures

To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that:

You first have to run the SET query. Afterwards the variables are available for the whole session. Then you can fire the SELECT with the custom variables. Another […]

Drop all tables from MySQL database

From time to time I stumbled over the case, that I had to drop all tables from a MySQL database. In phpMyAdmin or other GUI-based tools – in general – you only have the possibility to drop the whole database. But then you have to recreate it from the scratch.  Or you have to click-drop each table […]

Find duplicate entries in a MySQL Table

You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field:

This query will give you a list of all the entries having the same value for fieldname. It also will show the amount of duplicates.

Use column alias in conditions

With the use of HAVING you can use a result columns of a database query in itself:

Here the alias result (see AS ‘result’) for the only column is used as a condition with HAVING. That is by now the only way to compare a result column with another value in a query under […]

External access to MySQL database

To gain external access to MySQL database (f.e. in PHPSTorm or an external PHPMyAdmin) you can do: GRANT necessary privileges:

If the user already exists, you can leave the first line. Allow access from outside localhost:

On CentOS the my.cnf usually is directly located in /etc/ and the service name is mysqld. Check […]