kongkong的专栏

mysql字段脚本

【代码】mysql实现序列1。

创建function的时候，报这个错误。

mysql

Buffer PoolThe buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80%

基础数据员工表CREATE TABLE `employees1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `firstname` varchar(24) NOT NULL DEFAULT '' COMMENT 'firstname', `lastname` varchar(24) NOT NULL DEFAULT '' COMMENT 'las

脚本CREATE TABLE `account5` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11) NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL D

前言每次session使用之前，都要设置非自动提交set session autocommit=0;查看日志命令SHOW ENGINE INNODB STATUS ;脚本CREATE TABLE `account3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11)

前言每次session使用之前，都要设置非自动提交set session autocommit=0;查看日志命令SHOW ENGINE INNODB STATUS ;脚本CREATE TABLE `account3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11)

前言每次session使用之前，都要设置非自动提交set session autocommit=0;查看日志命令SHOW ENGINE INNODB STATUS ;脚本CREATE TABLE `account2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11)

前言每次session使用之前，都要设置非自动提交set session autocommit=0;查看日志命令SHOW ENGINE INNODB STATUS ;脚本CREATE TABLE `account2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11)

表结构CREATE TABLE t1( id int NOT NULL AUTO_INCREMENT, number int DEFAULT NULL, PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE t1 ADD INDEX t1_number_index (`number`)初始化数据insert into t1(id,number) values(1,2);insert into t1(id

Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to .

docker pull mariadb# 目录作为和容器的映射目录mkdir -p /data/mariadb/datadocker run --name mariadb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /data/mariadb/data:/var/lib/mysql -d mariadb--name 启动容器设置容器名称为mariadb- p 设置容器的3306端口映射到主机3306端口-e M...

拉取mysql镜像docker pull mysql:5.7启动主节点对外端口3339docker run -p 3339:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7启动从节点对外端口3340docker run -p 3340:3306 --name mysql-slave-1 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7查看正在运行的容器docker

NVL文档路径MariaDB starting with 10.3From MariaDB 10.3, NVL is a synonym for IFNULL.

crud统计数show status like 'com_select';show status like 'com_insert';show status like 'com_update';show status like 'com_delete';查看慢查询show variables like '%slow_query%';show variables like '%quer%';show global variables like '%long_query%';连接相关#

https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html语法xid: gtrid [, bqual [, formatID ]]gtrid: 全局事务idbqual: 局部事务idXA {START|BEGIN} xid [JOIN|RESUME]XA END xid [SUSPEND [FOR MIGRATE]]XA PREPARE xidXA COMMIT xid [ONE PHASE]XA ROLLBACK x

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is reco...

安装python3yum install python3安装py_innodb_page_infogit clone https://github.com/lynnlz/py_innodb_page_info查看ibd文件信息python3 py_innodb_page_info.py /var/lib/mysql/test/account0.ibd详细信息python3 py_innodb_page_info.py -v /var/lib/mysql/test/account.ibd

The Page Directory part of a page has a variable number of record pointers. Sometimes the record pointers are called "slots" or "directory slots". Unlike other DBMSs,InnoDBdoes not have a slot for every record in the page. Instead it keeps a sparse dire...

查看employee_1表desc employee_1总条数select count(*) from employee_1查看information_schema中相关表信息注意索引的PAGE_NO和:index_idSELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_sche..

8.2.1.11 Block Nested-Loop and Batched Key Access JoinsIn MySQL, a Batched Key Access (BKA) Join algorithm is available that uses both index access to the joined table and a join buffer. The BKA algorithm supports inner join, outer join, and semijoin ope

8.2.1.10 Multi-Range Read OptimizationReading rows using a range scan on a secondary index can result in many random disk accesses to the base table when the table is large and not stored in the storage engine’s cache. With the Disk-Sweep Multi-Range Rea

Outer joins include LEFT JOIN and RIGHT JOIN.外部连接包括左连接和右连接。MySQL implements an A LEFT JOIN B join_specification as follows:MySQL实现了A LEFT JOIN B JOIN 规范，如下所示：Table B is set to depend on table A and all tables on which A depends.表B被设置为依赖于表A和A所依赖的所有表.

8.2.1.6 Nested-Loop Join AlgorithmsMySQL executes joins between tables using a nested-loop algorithm or variations on it.MySQL使用嵌套循环算法或其变体在表之间执行连接。Nested-Loop Join Algorithm 嵌套循环连接算法A simple nested-loop join (NLJ) algorithm reads rows from the first

修改编码# 数据库alter database employees character set latin1;alter database employees character set utf8;alter database employees character set utf8mb4;# 表alter table employees character set latin1;alter table employees character set utf8;alter table em

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html8.2.1.5Index Condition Pushdown OptimizationIndex Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Witho.

8.2.1.4Engine Condition Pushdown Optimization此优化只能由NDB存储引擎使用https://dev.mysql.com/doc/refman/5.7/en/engine-condition-pushdown-optimization.htmlThis optimization improves the efficiency of direct comparisons between a nonindexed column and a constan.

13.7.5SHOW Statements13.7.5.1SHOW BINARY LOGS Statement列出服务器上的二进制日志文件SHOW BINARY LOGSSHOW MASTER LOGS13.7.5.2SHOW BINLOG EVENTS Statementhttps://dev.mysql.com/doc/refman/5.7/en/show-binlog-events.htmlSHOW BINLOG EVENTS [IN 'log_name'] [FR...

https://dev.mysql.com/doc/refman/5.7/en/analyze-table.htmlANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...ANALYZE TABLE performs a key distribution analysis and stores the distribution for the named table or tables. For MyISAM ta

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html13.7.2.4 OPTIMIZE TABLE SyntaxOPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data,

https://dev.mysql.com/doc/refman/5.7/en/checksum-table.html13.7.2.3CHECKSUM TABLE StatementCHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to ver.

8.2.1.3 Index Merge OptimizationThe Index Merge access method retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can .

The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index. The following sections describe conditions under which .

8.2.1.1WHERE Clause OptimizationThis section discusses optimizations that can be made for processing WHERE clauses. The examples use SELECT statements, but the same optimizations apply for WHERE clauses in DELETE and UPDATE statements.本节讨论可以为处理...

Queries, in the form of SELECT statements, perform all the lookup operations in the database. Tuning these statements is a top priority, whether to achieve sub-second response times for dynamic web pages, or to chop hours off the time to generate huge ove.

12.4.2Comparison Functions and OperatorsWhen an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers a.

11.3.6The SET TypeA SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with me.

11.3.5The ENUM TypeAn ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.ENUM是一个字符串对象，其值是从允许值的列表中选择的，这些值在表创建时在列规范中明确枚举。See Section 11.3....