MySQL [Oakfire Wiki]

本页面只读。您可以查看源文件，但不能更改它。如果您觉得这是系统错误，请联系管理员。

====== MySQL ======
  * Official site: [[http://mysql.com|mysql.com]]
===== MariaDB =====
  * Official site: [[https://mariadb.org/|mariadb.org]]
  * 与 mysql 的差异: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schema/
  * 在root下刚安装完时，即可直接用 ''mysql'' 命令进入， mariadb 会自动检查当前系统用户是否为root.
  * 执行 ''sudo mysql_secure_installation'' 脚本来进行安全设置
  * Ubuntu20.04 后 MariaDB 默认使用 ''unix_socket'' 插件来认证root用户，改回 ''mysql_native_password'' 插件的方式:<code bash>
sudo su # get root access
mysql # connect db
update mysql.user set plugin = 'mysql_native_password' where User = 'root';
flush privileges;
</code>
===== Tools =====
  * Mac 下好用的工具：[[https://www.sequelpro.com/|Sequal Pro]]
  * [[https://github.com/slashbaseide/slashbase|slashbase]]: 一个基于浏览器的数据库操作 IDE，有点类似 PHPMyAdmin，但使用 Go 语言编写，并且支持 PostgreSQL 和 MongoDB。
===== Manual =====
  * [[https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html|sql syntax]]
==== Connect ====
  * ''mysql -h host -u username -p password -P port''
  * default port,no password: ''mysql -h host -u username -p''
  * disconnect: ''mysql>  quit''
==== Create User ====
  * create user: <code>
mysql -h host -u username -p
mysql> use mysql;
mysql> insert into user(Host,User,Password) values("localhost","newname",password("newpassword"));
mysql> flush privileges;</code>
====  Database ====
  * Create: ''create database newdb;''
  * List databases: ''show databases;''
  * Use databases: '' use newdb;''
  * List tables: ''show tables''
  * show table : ''describe tablename'' or  ''desc tablename''
  * change table: ''ALTER TABLE''
      * change table name: ''ALTER TABLE tablename RENAME TO new_tablename'' 
      * remove column: ''ALTER TABLE tablename DROP COLUMN columnname''
      * add column: ''ALTER TABLE tablename ADD COLUMN columnname type''
      * modify column： ''ALTER TABLE tablename CHANGE columnname new_columnname new_type'';

==== Add Privages ====
  * 给 newname 用户添加 newdb 所有权限：<code sql>
grant all privileges on newdb.* to newname@localhost identified by 'newpassword';
flush privileges;
</code>
  * 不限数据库不限来源ip不指定密码的写法：<code sql>
grant all privileges on *.* to username@'%';
flush privileges; 
</code>

==== Check Connections ====
  * Connection history: ''show status like 'Conn%'; ''
  * Current connections: ''show status like '%onn%'; '' 
  * Threads connected: ''show processlist;''
==== Query ====
  * [[http://dev.mysql.com/doc/refman/5.7/en/examples.html|select examples]], ''select a,b from tablename where [conditions] order by a''
  * [[http://dev.mysql.com/doc/refman/5.7/en/delete.html|delete syntax]]; '' delete from tablename where conditions ''
  * [[http://dev.mysql.com/doc/refman/5.7/en/update.html|update syntax]]; '' update tablename set a=1 b=2 where conditions''
  * [[http://dev.mysql.com/doc/refman/5.7/en/insert.html|insert syntax]]; '' insert into tablename(colname1, colname2, ...) values(value1, value2, ...)''
  * The query mixes ASC and DESC:<code sql>SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;</code>ASC 可省略，默认正序。


==== Index ====
  * create index: 
    * ''ALTER TABLE table_name ADD INDEX index_name (column_list)''
    * ''ALTER TABLE table_name ADD UNIQUE (column_list)''
    * ''ALTER TABLE table_name ADD PRIMARY KEY (column_list)''
  * remove index:
    * ''DROP INDEX index_name ON talbe_name''
    * ''ALTER TABLE table_name DROP INDEX index_name''
    * ''ALTER TABLE table_name DROP PRIMARY KEY''
  * show index:''SHOW INDEX FROM table_name''

==== ORDER BY ====


===== Tips =====
  * 记录 mysql 命令行结果到文件<code bash>mysql> tee output.txt
Logging to file 'output.txt'
mysql> notee
Outfile disabled.
#或者
mysql> \T output.txt
Logging to file 'output.txt'
mysql> \t
Outfile disabled.
</code>
  * 导入导出数据: mysqldump, 查看 ''man mysqldump''
    * ''mysqldump --databases [dbname1] [dbname2]   > output.sql''
    * ''mysql < output.sql'' 
  * 获取时区设置 ''SHOW VARIABLES LIKE 'system_time_zone' ''
  * Timestamp 类型赋值给 Datetime 类型<code sql>update sometable set dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));</code>
  * 查看表大小<code sql>
 select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,INDEX_LENGTH from information_schema.TABLES;
</code>
  * [[http://coolnull.com/154.html|mysql binlog日志自动清理及手动删除]]
    * 关闭mysql主从，关闭binlog；
    * 开启mysql主从，设置expire_logs_days；
    * 手动清除binlog文件，''PURGE MASTER LOGS <params>''
  * DNS解析如果有问题的话会影响 mysql 连接速度，可在 ''my.cnf'' 中 ''[mysqld]'' 下添加 ''skip-name-resolve'' 来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。