Based on the answer by OMG Ponies, but using INFORMATION_SCHEMA.TABLES instead of INFORMATION_SCHEMA.COLUMNS. When looking around on the web, all I could find was info on the columns’ comments, but never on the table’s. This is how to get a table’s comment. SELECT table_comment FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=”my_cool_database” AND table_name=”user_skill”; +————————–+ | table_comment | +————————–+ … Read more
use lsof -i TCP:3306 to check which program binds port 3306
UPDATE users SET data = SUBSTR(data, 2); This will iterate through all rows in users and replace data with itself minus the first character.
Your query was nearly perfect 😉 Just try DELETE FROM table_name WHERE ID>9; You could also use DELETE FROM table_name WHERE ID>=10; As you already mention.
If you restrict access from remote hosts to your usernames and passwords then someone won’t be able to access the database externally. You could also configure your firewall to only allow traffic to 3306 (MySQL Default Port) from the localhost machine. Update To setup your user so they can only access through LOCALHOST use: GRANT … Read more
What you need to do is first, unpivot the data and then pivot it. But unfortunately MySQL does not have these functions so you will need to replicate them using a UNION ALL query for the unpivot and an aggregate function with a CASE for the pivot. The unpivot or UNION ALL piece takes the … Read more
Should be in wp_options table, the field is called db_version. So, yes, it’s possible. You can run this SQL command (substitute your table name if different): SELECT * FROM `wp_options` where option_name=”db_version” Make sure to consult the codex, as the db_version looks different from the wp version. for instance: For Version 3.1, the database version … Read more
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;
select en.id, fp.blogid, count(*) as occurrences from french.blog_pics fp, french.blog_news fn, english.blog_news en where fp.blogid = fn.id and en.title_fr = fn.title and fp.title != ” group by en.id having count(*) > 1
Yes, it is. You should query Performance Schema: select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema=”your_schema” The count_star column show how many times each index was used since MySQL was started. If you add the following, you got the never used indexes: and count_star = 0