![](https://img-blog.csdnimg.cn/20210924163746651.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
mysql
问题不大,必须掌握
问题不大的
礼多人不怪
展开
-
mysql deadlock
transaction isolation1. RU(read uncommitted)2. RC(read committed)3. RR(repeatable read)4. Serializablemysql提供了两种读取方式:快照度(snapshot read)和当前读(current read)current read包含三种情况:1. select ... lock in share mode 加S锁2. select ... for upd...原创 2020-09-03 15:29:18 · 158 阅读 · 0 评论 -
mysql diagnose problems
select a.DIGEST_TEXT as query, a.COUNT_STAR as exec_count, sec_to_time(a.SUM_TIMER_WAIT/1000000000000) as exec_time_total, sec_to_time(a.MAX_TIMER_WAIT/1000000000000) as exec_time_max, (a.AVG_TIMER_WAIT/1000000000) as exec_time_avg_ms, a.SUM_ROWS_SENT as r原创 2020-05-24 11:33:31 · 277 阅读 · 0 评论 -
mysql 索引字段,比较大小时不走索引
1. 表t_test中的age字段有普通索引create table t_test(id int not null, name varchar(32), age int, index(age)) ;2. 根据age字段查询数据select * from t_test where age < 20;问题及解析:该查询不一定走索引,和表的数据量有关系,只有在满足条件的数据占比较小时(占全部数据的比例)才会走索引...原创 2020-05-21 16:44:14 · 3797 阅读 · 0 评论 -
information_schema系统表——TABLE_CONSTRAINTS
表约束信息INFORMATION_SCHEMA NameSHOW NameRemarksCONSTRAINT_CATALOG defCONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA原创 2017-11-16 09:52:48 · 2417 阅读 · 0 评论 -
information_schema系统表——TABLE_PRIVILEGES
表权限The TABLE_PRIVILEGES table provides information about table privileges. This information comes from the mysql.tables_priv grant table.INFORMATION_SCHEMA NameSHOW NameR原创 2017-11-16 09:55:29 · 1535 阅读 · 0 评论 -
information_schema系统表——TRIGGERS
触发器The TRIGGERS table provides information about triggers. To see information about a table's triggers, you must have the TRIGGER privilege for the table.INFORMATION_SCHEMA Name原创 2017-11-16 09:59:24 · 978 阅读 · 0 评论 -
information_schema系统表——USER_PRIVILEGES
用户权限The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user grant table.INFORMATION_SCHEMA NameSHOW NameRemarks原创 2017-11-16 10:01:13 · 2889 阅读 · 0 评论 -
mysql timestamp和datetime 简单区别
1.timestamp存储和时区相关,即会根据时区做转换如:+8时区存进去, 而在+9时区取出,则时间为+9时区的时间而datetime存储和时区不相关,不会做转换如:+8时区存进去,在+9时区取出时,仍为+8时区的时间2.占用空间不一样,前者4字节,后者8字节 例子:create table t_timezone ( ts timestamp, dt datetime...原创 2018-11-08 10:35:28 · 2296 阅读 · 0 评论 -
### Cause: java.sql.SQLException: The table 'xxx' is full
### Cause: java.sql.SQLException: The table 'xxx' is full; uncategorized SQLException; SQL state [HY000]; error code [1114]; The table 'xxx' is full; nested exception is java.sql.SQLException: The ta...原创 2018-12-10 09:27:37 · 6752 阅读 · 0 评论 -
mysql create user
mysql 5.71.grant all privileges on *.* to 'username'@'%' identified by 'username123';select * from mysql.user;localhostrootlocalhostmysql.sys%usernamemysql81.create user 'usernam...原创 2019-03-10 19:33:23 · 6575 阅读 · 0 评论 -
mysql authentication plugin
default_authentication_pluginshow variables 'default_authentication_plugin';mysql 5.7: mysql_native_passwordmysql8: caching_sha2_password1.alter user authentication pluginalter user '...原创 2019-03-10 20:16:44 · 5785 阅读 · 0 评论 -
mysql alter variables
updated variable still take effect after mysql restart./var/lib/mysql/mysqld-autto.cnf;set persist password_require_current=on原创 2019-03-10 20:30:17 · 428 阅读 · 0 评论 -
information_schema系统表——TABLESPACES
表空间信息,该表不包含innodb表空间,INNODB_SYS_TABLESPACESand INNODB_SYS_DATAFILES从5.7.8开始,innodb表空间信息查看INFORMATION_SCHEMA.FILESThe TABLESPACES table provides information about active tablespaces.原创 2017-11-16 09:50:33 · 1184 阅读 · 0 评论 -
information_schema系统表——TABLES
数据库中的表信息The TABLES table provides information about tables in databases.INFORMATION_SCHEMA NameSHOW NameRemarksTABLE_CATALOG defTABLE_SCHEMA原创 2017-11-16 09:45:48 · 2020 阅读 · 0 评论 -
information_schema系统表——COLLATIONS
该表包含每个字符集的所有信息 The COLLATIONS table provides information about collations for each character set.INFORMATION_SCHEMA NameSHOW NameRemarksCOLLATION_NAMEColl原创 2017-11-15 14:44:49 · 893 阅读 · 0 评论 -
information_schema系统表——COLLATION_CHARACTER_SET_APPLICABILITY
该表描述了字符集适合哪种比对方法The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation. The columns are equivalent to the first two display fields that we ge原创 2017-11-15 15:07:07 · 325 阅读 · 0 评论 -
information_schema系统表——COLUMNS
该系统表包含了所有表的列信息The COLUMNS table provides information about columns in tables.INFORMATION_SCHEMA NameSHOW NameRemarksTABLE_CATALOG defTABLE_SCHEM原创 2017-11-15 15:30:42 · 1442 阅读 · 0 评论 -
information_schema系统表——COLUMN_PRIVILEGES
该表提供了有关列权限信息,这些信息来自mysql.columns_priv权限表The COLUMN_PRIVILEGES table provides information about column privileges. This information comes from the mysql.columns_priv grant table.I原创 2017-11-15 15:49:35 · 525 阅读 · 0 评论 -
information_schema系统表——ENGINES
存储引擎信息表The ENGINES table provides information about storage engines.INFORMATION_SCHEMA NameSHOW NameRemarksENGINEEngineMySQL extensionSUPPORT原创 2017-11-15 15:52:21 · 544 阅读 · 0 评论 -
information_schema系统表——EVENTS
调度计划事件信息表The EVENTS table provides information about scheduled events, which are discussed in Section 23.4, “Using the Event Scheduler”. The SHOW Name values correspond to column names of the原创 2017-11-15 15:58:18 · 1122 阅读 · 0 评论 -
information_schema系统表——FILES
包含了表空间数据存储的文件The FILES table provides information about the files in which MySQL tablespace data is stored.INFORMATION_SCHEMA.FILES provides information about InnoDB data files. In NDB C原创 2017-11-15 17:17:53 · 1693 阅读 · 0 评论 -
information_schema系统表——GLOBAL_STATUS and SESSION_STATUS
NoteAs of MySQL 5.7.6, the value of the show_compatibility_56 system variable affects the information available from the tables described here. For details, see the description of that variable原创 2017-11-15 17:54:31 · 962 阅读 · 0 评论 -
information_schema系统表——PROCESSLIST
进程列表The PROCESSLIST table provides information about which threads are running.INFORMATION_SCHEMA NameSHOW NameRemarksIDIdMySQL extensionUSER原创 2017-11-16 09:23:34 · 5561 阅读 · 0 评论 -
information_schema系统表——CHARACTER_SETS
该表包含了可用的字符集The CHARACTER_SETS table provides information about available character sets.INFORMATION_SCHEMA NameSHOW NameRemarksCHARACTER_SET_NAMECharset原创 2017-11-15 14:21:01 · 725 阅读 · 0 评论