- 查看数据库编码
show variables like ‘character%’ - 设置数据库编码
set names ‘utf8’ - 导入批量sql文件
source ${file_path}\${file_name} - 删除数据表的主键
alter table ${table_name} drop primary key - 将SQL执行结果导出到文件
select * from mytable into outfile “D:/result.txt”; - 修改字段名称
ALTER TABLE message CHANGE msgId msg_id varchar(255) UNIQUE NOT NULL - 创建和删除用户
创建:CREATE USERuser01
@localhost
IDENTIFIED BY ‘88888888’;
删除:DROP USER user01; - 按前缀批量删除表格
执行下列查询操作
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';
将查询结果复制运行即可。
9.