mysql split string by comma and count
SELECT (LENGTH(`table`.`fieldname`) - LENGTH( REPLACE( `table`.`fieldname` , ',', '' ) ) + 1) AS `number` FROM `table` WHERE TRIM( IFNULL( `table`.`fieldname` , '' ) ) > ''
I had an issue while migrating (My)SQL data back and forth from Linux and Windows. When ever I made come changes on my local windows dev system and tried to apply the changes to the production system the SQL statement failed. Later I noticed that the table names on Windows were no matter what I tried in lower case. The MariaDB docs told me that the table names on Windows where always lower case and case insensitive. What a bummer.
Searching I found
lower_case_table_names in the docs. With the values 0,1 and 2.
0 (Unix) = table names and aliases and database names are compared in a case-sensitive manner.
1 (Windows) = names are stored in lowercase and not compared in a case-sensitive manner.
2 (Mac OS X) = names are stored as declared, but compared in lowercase.
I tried 0 and got
[ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory resides on a case-insensitive file system. Please use a case sensitive file system for your data directory or switch to a case-insensitive table name mode. [ERROR] Aborting
But setting it to 2 works. No longer problems
[mysqld] datadir=C:/Program Files/MariaDB 10.2/data port=3306 innodb_buffer_pool_size=1003M character-set-server=utf8 performance_schema = ON lower_case_table_names = 2 [client] port=3306 plugin-dir=C:/Program Files/MariaDB 10.2/lib/plugin
I wonder cause in the old days of using MySQL it never was a problem.
Today I noticed that the commentmeta table in the DB was huge, even the fact that I delete my spam daily. I saw that the commentmeta table had a size of 1,5 MB compared to the other tables with a few kb. I figured out that Akismet plugin caused that. I love that plugin cause it saved me a lot of time filtering the spam comments, but blowing my db is another thing. I have more than this blogs running with much more content. There is might be more trouble with the increase of the table.
However I executed the following query and the size of that table was only 7 kb. Woot!
DELETE FROM `wp_commentmeta` WHERE `meta_key` = 'akismet_as_submitted' OR `meta_key` = 'akismet_history' OR `meta_key` = 'akismet_rechecking' OR `meta_key` = 'akismet_result' OR `meta_key` = 'akismet_user' OR `meta_key` = 'akismet_user_result';
Geht ziemlich schnell Sonderzeichen zu reparieren in MySQL! Scheiß Encoding! ;)
UPDATE `table` set `column`= REPLACE(`column`,"ÃŸ", "ß"), `column`= REPLACE(`column`, "Ã¤", "ä"), `column`= REPLACE(`column`, "Ã¼", "ü"), `column`= REPLACE(`column`, "Ã¶", "ö"), `column`= REPLACE(`column`, 'Ã„', 'Ä'), `column`= REPLACE(`column`, "Ãœ", "Ü"), `column`= REPLACE(`column`, "Ã–", "Ö"), `column`= REPLACE(`column`, 'â‚¬', '€');