https://kb.unixservertech.com/ 2026-05-12T21:06:41+00:00 https://kb.unixservertech.com/ https://kb.unixservertech.com/_media/wiki/dokuwiki.svg text/html 2020-09-26T00:56:13+00:00 Anonymous (anonymous@undisclosed.example.com) https://kb.unixservertech.com/software/mysql/altering_tables?rev=1601081773&do=diff Altering Tables Adding a column To add a column if it does not exist you can do a Prepare/Execute function as follows: SET @dbname = DATABASE(); SET @tablename = "tableName"; SET @columnname = "colName"; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_name = @tablename) AND (table_schema = @dbname) AND (column_name = @columnname) ) > 0, "SELECT 1", CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " … text/html 2022-11-10T05:07:46+00:00 Anonymous (anonymous@undisclosed.example.com) https://kb.unixservertech.com/software/mysql/pivots?rev=1668056866&do=diff Create a pivot result in mariadb/mysql This relies heavily on <https://stackoverflow.com/questions/15997090/crosstab-view-in-mysql> and I even stole the data structure. I just worked to figure out what was going on, then wrote this up. NOTE: it is possible I made a mistake in my interpretation. If so, it is all on me. text/html 2022-11-10T05:08:59+00:00 Anonymous (anonymous@undisclosed.example.com) https://kb.unixservertech.com/software/mysql/tuning?rev=1668056939&do=diff mariaDB/mySQL Tuning mySQL and mariaDB come stock with a few defaults, but sometimes it is better to modify them yourself to tune the system more to your usage. Following is based on a stand alone server with 16G of memory. The system is a central repository for multiple machines, so it needs to be accessible to other machines in the network. text/html 2026-02-07T04:05:29+00:00 Anonymous (anonymous@undisclosed.example.com) https://kb.unixservertech.com/software/mysql/withrecursive?rev=1770437129&do=diff The WITH RECURSIVE syntax Using FrontAccounting, I needed a way to project recurring revenue for the next 12 months. I had a combination of a Perl script and a MySQL query to do this, but I wanted a single query that I could plug into my report tool. Warnings Just like it says, this is recursion, generally used to retrieve data from a hierarchical configuration.