MySQL

多表删除

Delete multiple tables

SELECT CONCAT("DROP TABLE ", GROUP_CONCAT(table_name), ";") FROM information_schema.tables WHERE table_schema = "DATABASE_NAME" AND table_name LIKE "PREFIX_TABLE_NAME%";

创建用户

Create user

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';

修改权限

Update privilege

grant all privileges on database_name.* to 'user_name'@'host' identified by 'user_password';

字符串替换

MySQL REPLACE

UPDATE `table_name` SET `field_name` = REPLACE(`field_name`,'from_str','to_str');

忽略外键约束

Ignore FOREIGN KEY

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE your_table_name;

SET FOREIGN_KEY_CHECKS = 1;

更新数据库中每个表的CHARACTER

Update CHARACTER every table in database

#!/bin/bash

charset='utf8'

MYSQL_HOST=your_mysql_host
MYSQL_DB=your_database_name
MYSQL_USER=your_database_user
MYSQL_PASS=your_database_password

echo "Changing charset of database: $MYSQL_DB"
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -s -e "ALTER DATABASE $MYSQL_DB CHARACTER SET = $charset;"

TABLES=$(echo SHOW TABLES | mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB)

for table in $TABLES
do
  echo ''
  echo "Changing charset of table: $table"
  echo mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -s -e "ALTER TABLE \`$table\` CHARACTER SET $charset"
  mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -s -e "ALTER TABLE \`$table\` CHARACTER SET $charset"

  echo "Converting charset of table: $table"
  mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -s -e "ALTER TABLE $table CONVERT TO CHARACTER SET $charset"
done

echo ''
echo 'Conversion done!'

MySQL 权限备忘

MySQL privileges