Category Archives: MySQL

MySQL, Database table history using Schemas and triggers

Assuming that we have to trace every intervention on a database called « stats»

As result we wil have a new database with the “history_” prefix which will contain all the same tables of our source database “stats” but with different column definition. We have to add 3 new colums.

  1. Operation.
  2. Time.
  3. Operation made.
  4. Creating the histroy database.

To continue we need to be sure that the mysql user has all privilieges for creating databases and Triggers.

Continue reading MySQL, Database table history using Schemas and triggers

MySQL, Truncated result with GROUP_CONCAT function.

MySQL function group_concat is a powerful tool to concat strings using a custom sperator :

 

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])

But sometimes the execution result may be truncated because of mysql default setting, so in this case “group_concat_max_len” haveto be changed to a bigger value

SET [GLOBAL | SESSION] group_concat_max_len = val;