Home
|
简体中文
|
繁体中文
|
杂文
|
Github
|
知乎专栏
|
Facebook
|
Linkedin
|
Youtube
|
打赏(Donations)
|
About
部分 III. MySQL
上一页
下一页
知乎专栏
部分 III. MySQL
目录
20. MySQL Server
20.1. MySQL 安装
20.1.1. Rocky Linux
20.1.2. CentOS
20.1.3. Docker
20.1.4. Ubuntu/Debian
20.1.5. 源码安装
20.1.6. 二进制版本
20.1.7. Installing MySQL on Linux Using the MySQL Yum Repository
20.1.8. Firewall
20.1.9. Mac OS
20.1.10. 重置数据库
20.1.11. MariaDB
20.1.12. Percona
20.2. MySQL Plugin
20.2.1. validate_password
20.2.2. MySQL Images manager
20.2.3. MySQL fifo
20.2.4. 内容输出到文本插件
20.3. Replication
20.3.1. Master Slave
20.3.2. Master Master(主主)
20.3.3. Semisynchronous Replication
20.3.4. multi-master replication
20.3.5. multi-source replication
20.3.6. 与复制有关的问题
20.3.7. GTID
20.4. MySQL Custer
20.4.1. Management node (MGM node)
20.4.2. Data node
20.4.3. SQL node
20.4.4. Starting
20.4.5. Shutdown
20.4.6. Testing
20.5. MySQL Proxy
20.5.1. Ubuntu
20.5.2. CentOS
20.6. MySQL Router
20.6.1. 安装 MySQL Router
20.6.2. 配置 MySQL Router
20.6.3. MySQL Router ， Haproxy，LVS 的选择
20.7. my.cnf
20.7.1. bind-address
20.7.2. 禁用TCP/IP链接
20.7.3. 配置字符集
20.7.4. 最大链接数 max_connections
20.7.5. 默认引擎 storage-engine
20.7.6. max_allowed_packet
20.7.7. skip-name-resolve
20.7.8. timeout
20.7.9. 与复制有关的参数
20.7.10. 与 InnoDB 有关的配置项
20.7.11. EVENT 设置
20.7.12. 日志
20.7.13. MySQL 5.7 my.cnf 实例
20.7.14. Example for my.cnf
20.8. variables
20.8.1. 查询多个变量
20.8.2. time_zone
20.8.3. sql_mode
20.8.4. wait_timeout
20.8.5. table_lock_wait_timeout
20.8.6. low_priority_updates
20.8.7. collation_server
20.8.8. character_set
20.8.9. datadir
20.8.10. plugin_dir
20.8.11. storage_engine
20.8.12. timeout
20.8.13. max_connections
20.8.14. 自动提交 autocommit
20.9. Monitoring
20.9.1. Analysis and Optimization
20.9.2. Cacti
20.9.3. Monitoring MySQL with SNMP
21. Client and Utility Programs
21.1. mysql - the MySQL command-line tool
21.1.1. ~/.my.cnf
21.1.2. 屏幕输出到文件
21.1.3. 终端编码
21.1.4. Unix Socket
21.1.5. 重定向巧用
21.1.6. --sigint-ignore 忽略 Ctrl + C
21.1.7. mysql log
21.2. mysqldump - a database backup program
21.2.1. 备份数据库结构和数据
21.2.2. 备份数据库结构和数据并压缩文件
21.2.3. 备份到文件
21.2.4. 备份数据库结构/表
21.2.5. 备份数据库，无结构，只有数据
21.2.6. 备份所有数据库
21.2.7. 使用完整的insert插入数据
21.2.8. --extended-insert / --skip-extended-insert
21.2.9. --skip-lock-tables
21.2.10. --skip-add-locks
21.2.11. --where
21.2.12. 注释信息--comments /--skip-comments
21.2.13. 不导出注释信息
21.2.14. 字符集设置
21.3. mysqladmin - client for administering a MySQL server
21.3.1. reload
21.3.2. 更改密码
21.3.3. status
21.3.4. process list
21.4. myisamchk — MyISAM Table-Maintenance Utility
21.5. mysqlcheck — A Table Maintenance and Repair Program
21.6. mysqlslap - load emulation client
21.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
21.8. mysql-shell
21.9. mycli
21.10. MySQL慢查询日志（Slow Query Log）
21.10.1. MySQL 8.x
21.10.2. MySQL 5.x
21.11. mysql-admin
21.12. MySQL Workbench 数据库恢复
22. 数据库管理（Database Administration)
22.1. 用户管理（User Account Management）
22.1.1. 创建用户
22.1.2. 删除用户
22.1.3. 修改用户名
22.1.4. 修改密码
22.2. Access Privilege System
22.2.1. SHOW GRANTS
22.2.2. show privileges
22.2.3. Grant privileges
22.2.4. Revoke privileges
22.2.5. Show Privileges
22.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
22.2.7. Table Privileges
22.2.8. Column Privileges
22.3. 字符集转换
22.3.1. 转换 latin1 到 UTF-8
22.4. 重新整理AUTO_INCREMENT字段
22.5. 数据库内容替换
22.6. Kill 脚本
22.7. MySQL 时区管理
22.8. SHOW COMMAND
22.8.1. 查看版本
22.8.2. status
22.8.3. show processlist
22.8.4. 线程的使用情况
22.8.5. DATABASES
22.8.6. 排序统计信息
22.8.7. Key 状态
22.8.8. FUNCTION
22.8.9. PROCEDURE
22.8.10. TRIGGERS
22.8.11. EVENTS
22.8.12. 引擎(ENGINES)
22.8.13. 字符集(Collation)
22.8.14. SHOW GRANTS
22.8.15. validate_password
22.9. Maintenance 数据库维护
22.9.1. CHECK 检查表
22.9.2. ANALYZE 分析表
22.9.3. CHECKSUM
22.9.4. OPTIMIZE 优化表
22.9.5. REPAIR 修复
22.10. INFORMATION_SCHEMA
22.10.1. 查询表字段
22.10.2. 列出所有触发器
22.10.3. 查看表数据尺寸
22.11. Backup and Recovery
22.11.1. Import / Export
22.11.2. Snapshot Backup
23. DDL - Data Definition Language
23.1. 数据库管理(Database)
23.1.1. 创建数据库
23.1.2. 删除数据库
23.1.3. 修改数据库
23.1.4. 重命名数据库
23.1.5. 修改字符集
23.1.6. 查看数据库创建语句
23.2. 表管理(Table)
23.2.1. 数据类型
23.2.2. 基于现有表结构创建新空表
23.2.3. 基于已存在表创建新表
23.2.4. 修改表
23.2.5. 临时表
23.2.6. CHARACTER
23.2.7. DEFAULT
23.2.8. KEY
23.2.9. AUTO_INCREMENT 定义初始值
23.2.10. COMMENT
23.2.11. 修改表名
23.2.12. Engine 存储引擎
23.3. Partitioning
23.3.1. RANGE
23.3.2. LIST
23.3.3. HASH
23.3.4. KEY分区
23.3.5. Subpartitioning
23.3.6. 分区管理
23.3.7. EXPLAIN PARTITIONS
23.3.8. SHOW CREATE TABLE
23.3.9. INFORMATION_SCHEMA.partitions 表
23.3.10. 分区数据操作
23.4. Index
23.4.1. SHOW INDEX
23.4.2. CREATE INDEX
23.4.3. DROP INDEX
23.4.4. rebuild
23.5. 外键(Foreign Key)
23.5.1. FOREIGN KEY (RESTRICT)
23.6. 视图(View)
23.7. 存储过程(PROCEDURE)
23.7.1. 存储程序
23.7.2. EXECUTE 执行 SQL
23.7.3. PREPARE 传递参数
23.7.4. 存储过程返回数据
23.7.5. 结果集转JSON
23.7.6. 例子·过程返回结果
23.8. 函数
23.8.1. TIMESTAMP TO ISO8601
23.9. 触发器(Trigger)
23.9.1. create trigger
23.9.2. drop trigger
23.9.3. show triggers
23.9.4. EXAMPLE
23.10. 事件调度器(EVENT)
23.10.1. 启用 EVENT
23.10.2. 创建 EVENT
23.10.3. 禁用/启用
23.10.4. 查看 events
23.10.5. 删除 EVENT
23.10.6. EVENT 应用案例
24. DML (Data Manipulation Language)
24.1. INSERT 插入
24.1.1. INSERT INTO ... SELECT
24.1.2. INSERT IGNORE
24.1.3. INSERT...ON DUPLICATE KEY UPDATE
24.2. REPLACE 替换
24.3. DELETE 删除
24.3.1. 删除重复数据
24.3.2. 删除重复数据保留第一条
25. SQL Statement Syntax
25.1. DISTINCT
25.2. JOIN
25.2.1. LEFT JOIN / LEFT OUTER JOIN
25.2.2. JOIN / INNER JOIN
25.3. order by 排序
25.3.1. 处理 null
25.4. group by
25.4.1. 忽略 NULL
25.5. HAVING
25.6. REGEXP
25.7. IN / NOT IN
25.8. ALL / Any
25.9. exists, not exists
25.10. UNION
25.10.1. UNION ALL
25.10.2. 两张表字段不对等解决方法
25.11. OUTFILE/LOAD DATA INFILE
25.11.1. Export data to CSV from MySQL
25.11.2. Import data from CSV file.
25.12. CASE Syntax
25.13. 查询结果放入变量
25.14. MySQL 专有命令
25.14.1. SQL_NO_CACHE
25.14.2. SIGNAL Syntax
25.15. SQL 92
26. Functions and Operators
26.1. COUNT
26.2. UUID()
26.3. 字符串
26.3.1. LEFT/RIGHT
26.3.2. RPAD/LPAD
26.3.3. CONCAT
26.3.4. CONCAT_WS
26.3.5. 链接所有字段
26.3.6. GROUP_CONCAT
26.3.7. replace 字符串替换
26.3.8. SUBSTRING
26.3.9. SUBSTRING_INDEX
26.3.10. AES_ENCRYPT / AES_DECRYPT
26.4. Date and Time
26.4.1. year/month/day hour:minite:second
26.4.2. Unix time
26.4.3. DATE_FORMAT
26.4.4. DATE_SUB/DATE_ADD
26.4.5. datediff / timediff
26.5. 数值函数
26.5.1. cast 类型转换
26.5.2. truncate 保留小数位数
26.5.3. MOD 求余
26.6. Control Flow Functions
27. DCL (Data Control Language)
27.1. 锁
27.1.1. 共享锁
27.1.2. 排他锁
27.1.3. 锁
27.1.4. 锁等待与超时
27.2. 事务处理和锁定语句
27.2.1. 事务隔离级别
27.2.2. 事务所用到的表
27.2.3. 解决更新冲突
27.2.4. 共享锁
27.2.5. SAVEPOINT
28. Optimization
28.1. Limit 状态
28.2. 使用 Btrfs 文件系统存储mysql数据
28.3. 打开表的数量
28.4. Buffering and Caching
28.4.1. Query Cache SELECT Options
28.5. where 优化
28.6. SHOW PROFILE Syntax SQL性能分析器
28.7. PROCEDURE ANALYSE()
29. MySQL Connectors
29.1. JDBC
29.2. ODBC
29.3. MySQL native driver for PHP - mysqlnd
29.4. python-mysqldb
30. MySQL GUI/Web Manager
30.1. HeidiSQL
30.2. Toad for MySQL Freeware
30.3. phpMyAdmin - MySQL web administration tool
30.4. Maatkit Essential command-line utilities for MySQL
31. Miscellaneous
31.1. Multi-Master Replication Manager for MySQL
31.2. MHA
31.3. HandlerSocket
31.4. Maatkit
31.5. Mroonga
31.6. Amoeba
32. FAQ
32.1. Reset root password 重置MySQL root密码
32.1.1. MySQL 5.7.x
32.1.2. MySQL 8.0
32.2. 查看错误代码
32.2.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
32.2.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
32.3. 临时表是否需要建索引
32.4. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
32.5. Table 'performance_schema.session_variables' doesn't exist
32.6. SQL Error (1038): Out of sort memory, consider increasing server sort buffer size
32.7. this is incompatible with sql_mode=only_full_group_by
32.8. ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes
32.9. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
32.10. ERROR 1114 (HY000): The table 'your_table' is full
32.11. Error Code: 1146. Table 'test.CACHE_UPDATE' doesn't exist
32.12. Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
32.13. ERROR 1273 (HY000) at line 3364: Unknown collation: 'utf8mb4_0900_ai_ci'
32.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
32.15. ERROR 1364: 1364: Field 'id' doesn't have a default value
32.16. Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'create_time' at row 95692
32.17. ERROR 1415: Not allowed to return a result set from a trigger
32.18. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
32.19. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
32.20. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
32.21. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
32.22. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
32.23. ERROR 2013 (HY000): Lost connection to MySQL server during query
32.24. ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
32.25. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
32.26. Error Code: 3780. Referencing column 'id' and referenced column 'id' in foreign key constraint 'FKfeti3hcwwku7l7xqru4emwkc6' are incompatible.
32.27. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
32.28. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
32.29. mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
32.30. this is incompatible with sql_mode=only_full_group_by
32.31. mysqldump: [Warning] Using a password on the command line interface can be insecure.
32.32. mysql: [Warning] Using a password on the command line interface can be insecure.
32.33. 时间自动被加一秒
