For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
OR
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
Notes:
- There is no space between the option
-p
and the password. If your database has no password, remove the -u username -ppassword
part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema
error). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
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 imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
Best Answer
(Note: For mysql-5.6+ this won't work. There's a solution that applies to mysql-5.6+ if you scroll down or click here.)
If you don't want or cannot restart the MySQL server you can proceed like this on your running server:
mysql
database