1. 查看创建数据表的SQL语句、存储引擎和字符编码等信息:
SHOW CREATE TABLE tablename;
2. 修改数据库的字符编码:
ALTER DATABASE dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
修改表的字符编码:
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4;
修改表字段的字符编码:
ALTER TABLE tablename MODIFY columnname varchar(1000) CHARACTER SET utf8mb4;
3. 查看索引:
SHOW INDEX FROM tablename;
创建索引:
CREATE INDEX indexname ON tablename(columnname1,columnname2...);
删除索引:
DROP INDEX indexname on tablename;
4. 压缩导出表结构和数据的命令:
mysqldump -uroot -p password dbname tablename | gzip > tablename.sql.gz;
5. 修改表名:
ALTER TABLE tablename RENAME TO newtablename;
修改字段名:
ALTER TABLE tablename RENAME COLUMN columnname TO newcolumnname;
6. 增加字段:
ALTER TABLE tablename ADD COLUMN columnname longtext after columnname2;
修改字段:
ALTER TABLE tablename MODIFY COLUMN columnname timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
删除字段:
ALTER TABLE tablename DROP COLUMN columnname;
7. 查看当前所有连接的详细资料:
mysqladmin -h127.0.0.1 processlist
只查看当前连接数(Threads就是连接数.):
mysqladmin -h127.0.0.1 status