MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; This and other approaches … Read more
For those people still arriving at this question in 2020 or later, there are newer options that may be better than both of these. For example, utf8_unicode_520_ci. All these collations are for the UTF-8 character encoding. The differences are in how text is sorted and compared. _unicode_ci and _general_ci are two different sets of rules … Read more
For MySQL 5.0.3 and higher, you can use BIT. The manual says: As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64. Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which … Read more
From Save MySQL query results into a text or CSV file: SELECT order_id,product_name,qty FROM orders WHERE foo = ‘bar’ INTO OUTFILE ‘/var/lib/mysql-files/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’; Note: That syntax may need to be reordered to SELECT order_id,product_name,qty INTO OUTFILE ‘/var/lib/mysql-files/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES … Read more
Use this query: SELECT User FROM mysql.user; Which will output a table like this: +——-+ | User | +——-+ | root | +——-+ | user2 | +——-+ As Matthew Scharley points out in the comments on this answer, you can group by the User column if you’d only like to see unique usernames.
You can use GROUP_CONCAT: SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ‘, ‘) FROM peoples_hobbies GROUP BY person_id; As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates: SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ‘, ‘) FROM peoples_hobbies GROUP BY person_id; As Jan stated in their comment, you can also sort the values before … Read more
You can reset the counter with: ALTER TABLE tablename AUTO_INCREMENT = 1 For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel): Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if … Read more
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: SELECT id, MAX(rev) FROM YourTable GROUP BY id It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row … Read more
Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins. Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?. Find original one at: Difference between JOIN and OUTER JOIN in MySQL.
Try: mysql -u username -p database_name < file.sql Check MySQL Options. Note 1: It is better to use the full path of the SQL file file.sql. Note 2: Use -R and –triggers to keep the routines and triggers of original database. They are not copied by default. Note 3 You may have to create the … Read more