Data Storage: Specify the utf8mb4 character set on all tables and text columns in your database. This makes MySQL physically store and retrieve values encoded natively in UTF-8. Note that MySQL will implicitly use utf8mb4 encoding if a utf8mb4_* collation is specified (without any explicit character set). In older versions of MySQL (< 5.5.3), you’ll … 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
TL;DR: pip install -Iv (i.e. pip install -Iv MySQL_python==1.2.2) What these options mean: -I stands for –ignore-installed which will ignore the installed packages, overwriting them. -v is for verbose. You can combine for even more verbosity (i.e. -vv) up to 3 times (e.g. -Ivvv). For more information, see pip install –help First, I see two … 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