I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, “A”, 19); Let’s say the unique key is ID, and in my Database, there is a row with ID = … Read more
I can run this query to get the sizes of all tables in a MySQL database: show table status from myDatabaseName; I would like some help in understanding the results. I am looking for tables with the largest sizes. Which column should I look at? 19 s 19 You can use this query to show … Read more
I’m trying to update the column visited to give it the value 1. I use MySQL workbench, and I’m writing the statement in the SQL editor from inside the workbench. I’m writing the following command: UPDATE tablename SET columnname=1; It gives me the following error: You are using safe update mode and you tried to … Read more
The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall. This needs … Read more
There is a table messages that contains data as shown below: Id Name Other_Columns ————————- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * from messages group by name, I will get the result as: 1 A A_data_1 4 … Read more
Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance? 8
Since MySQL doesn’t seem to have any ‘boolean’ data type, which data type do you ‘abuse’ for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP script. Over time I have used and seen several approaches: tinyint, varchar fields containing the values 0/1, varchar fields containing the strings … Read more
Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here’s what I’m doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e ‘s/ /,/g’ | tee list.csv select id, concat(“\””,name,”\””) as name from students EOQ It gets messy when … Read more
I’m using the MySQL command-line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this? I’m using MySQL version 5.4.1. 1 16 Use this query: SELECT User FROM mysql.user; Which will output a table like this: +——-+ | User | +——-+ | root … Read more
Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I’m selecting multiple values from multiple tables, and after all the joins I’ve got a lot more … Read more
