![]() ![]() Prepared statement needs to be re-prepared.Īdd the following two lines to /etc/mysql//mysqld.cnf or wherever your cnf is or save them in a separate file and copy into the conf.d directory.You can now do CALL findAll('foobar') Įxcept not. " WHERE `",c1.COLUMN_NAME,"` LIKE '%", search, "%'" SEPARATOR "\nUNION\n") AS col "CONCAT_WS(',', ", (SELECT GROUP_CONCAT(c2.column_name) FROM `information_schema`.`columns` c2 WHERE c1.TABLE_SCHEMA=c2.TABLE_SCHEMA AND c1.TABLE_NAME=c2.TABLE_NAME AND c2.COLUMN_KEY='PRI') ,") AS pri,",Ĭ1.COLUMN_NAME, " AS value FROM ", c1.TABLE_NAME, ![]() SET SESSION group_concat_max_len := GROUP_CONCAT( DROP PROCEDURE IF EXISTS findAll ĬREATE PROCEDURE findAll( IN `search` TEXT ) Here's an actual, working, tested, simple to use answer building on multiple previous answers but also adding the primary key to the results. I want to search in all fields from all tables of a MySQL database for a given stringĪnwsers include GUIs, vague ideas, syntax errors, procedures needing table names or prefixes and all sorts of contortions. It's been twelve years and no one posted an answer to the following question: (you may have people at your desk with the quickness.) You are concerned with causing Table-locks (keep an eye on your client-connections).SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' = DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' I'm sure there are different ways you may go about doing this but here’s what works for me: - = DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements. ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN (\'%someText%\') ')Ģ) You can then just Right Click and use the Copy Row (tab-separated)ģ) Paste results in a new query window and run to your heart's content.ĭetail: I exclude system schema's that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked. # MISUSE AND YOU COULD CRASH A LARGE SERVERĬONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, # YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT # BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING ' WHERE ', A.COLUMN_NAME, ' LIKE \'%stuff%\' ')ĪND A.TABLE_SCHEMA != 'performance_schema'ĪND A.TABLE_SCHEMA != 'information_schema' # BE ADVISED USE ANY OF THESE WITH CAUTIONĬONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, I would suggest a three-step approach like this:ġ) Where this query builds a bunch of queries as a result set. If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |