This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and closes the database connection.
This guide walks through building a Spring Boot and MySQL‑backed “burn after viewing” image sharing service, covering architecture, environment setup, entity and repository design, upload and automatic deletion logic, Thymeleaf UI with burn animation, error handling, optimization, and Docker deployment.
This article examines how InnoDB acquires row‑level locks during INSERT … ON DUPLICATE KEY operations under REPEATABLE‑READ and READ‑COMMITTED isolation levels, explains the lock types on primary and unique indexes, and shows the rollback and lock‑conversion process with concrete SQL examples.
This article describes how a senior architect designed, configured, and implemented a 50‑table sharding solution for loan and repayment request data using ShardingSphere, Spring Boot 3, MySQL, and custom synchronization scripts, while also addressing historical data migration, backend query changes, and operational safeguards.
This article explains why using Docker to host MySQL is generally discouraged, highlighting challenges in database scaling due to container‑exclusive storage, difficulties sharing data files, and memory contention among containers, while also outlining possible synchronization solutions and scenarios where containerized MySQL may still be viable.
MySQL 8.3 introduces a new iterator‑aware JSON output for EXPLAIN and EXPLAIN ANALYZE, selectable via the explain_json_format_version variable, which aligns JSON with the tree format, provides richer execution statistics, and can be accessed programmatically through EXPLAIN INTO across all recent MySQL releases.
This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.
This article examines why a MySQL table with a case‑insensitive collation returns uppercase brand names when searching for lowercase input, analyzes the underlying charset and collation settings, and proposes backend pagination with a case‑insensitive fuzzy search and a unique index to reliably prevent duplicate brand records.
This article analyzes the detailed steps and timing of establishing and closing a MySQL connection from Java, using Wireshark traces to show that even a minimal connection can consume over 200 ms, highlighting the need for connection pooling in high‑traffic web applications.
This tutorial walks you through downloading MySQL binaries, creating a dedicated mysql user, extracting files to /usr/local, setting up separate data directories, initializing each instance, configuring my.cnf, managing the services with systemd, setting root passwords, and troubleshooting common errors, enabling you to run three isolated MySQL servers on a single host.
Effective database administration relies on continuous monitoring of system resources—CPU, memory, disk I/O, and network—using tools like top, iostat, and vmstat, alongside logging slow MySQL queries, analyzing performance bottlenecks, and following best practices such as automated monitoring, centralized log management, regular audits, and log backups.
This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed sorting, misuse of EXISTS, condition push‑down limitations, early result narrowing, intermediate result push‑down, and demonstrates how rewriting queries with JOINs, derived tables, and WITH clauses can dramatically improve execution speed.
This article explains why a MySQL 8 InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL or when logical deletion is used—and presents several practical strategies, including status counters, timestamps, extra IDs, hash fields, and batch‑insert techniques, to enforce true uniqueness.
This article introduces three free, open-source MySQL client tools—MySQL Workbench, DBeaver, and phpMyAdmin—detailing their main features, cross‑platform support, and suitability for developers and administrators seeking powerful yet user‑friendly database management solutions.
This article describes how a senior architect designed and implemented a sharding solution for loan and repayment tables, covering database design, table naming, sharding algorithms, historical data migration, three‑write synchronization, dynamic switches, and the necessary Spring Boot, MyBatis‑Plus, and ShardingSphere configurations with code examples.
This article presents nine practical strategies—including adding default filters, limiting page size, reducing joins, optimizing indexes, using straight_join, archiving data, leveraging count(*), querying ClickHouse, and implementing read‑write splitting—to dramatically improve the speed and scalability of pagination APIs in MySQL‑based back‑ends.
The article explains cache‑aside consistency challenges and compares four update strategies—DB‑then‑cache, cache‑then‑DB, DB‑then‑delete, and delete‑then‑DB—showing that deleting the cache after a successful DB write offers the smallest inconsistency window, while recommending TTLs, message‑queue invalidation, and multi‑key coordination for robust eventual consistency.
Using an unsigned INT to store IPv4 addresses in MySQL saves space and enables efficient range queries, while strings are larger and slower; the article explains these advantages, outlines conversion functions INET_ATON/INET_NTOA, shows equivalent handling for IPv6, and provides Java utilities for bidirectional conversion.
This guide introduces Docker fundamentals, explains how containers differ from virtual machines, walks through installing Docker on Linux, and demonstrates practical steps to pull and run a MySQL container, while also providing a handy reference of common Docker commands for managing containers and images.
The article recounts a recent Hikvision interview, outlining campus salary ranges and providing a technical Q&A that explains Spring Boot’s startup sequence, Spring MVC request handling, MySQL index structures, creation rules, and performance diagnostics, as well as Redis’s in‑memory, single‑threaded architecture and optional I/O threading.
This article presents a step‑by‑step guide on building a Snapchat‑style “burn after reading” image sharing system using Spring Boot, MySQL, Thymeleaf, and optional cloud storage, covering background analysis, architecture design, environment setup, code implementation, optimization, testing, and deployment.
This article examines a puzzling MySQL case‑insensitive collation issue where a user‑defined brand "yoyo" cannot be saved because the database returns an existing uppercase "YOYO" entry, explains the underlying COLLATE settings, and proposes practical solutions to prevent duplicate brand data.
This article explains how MySQL 8.0.32 InnoDB handles INSERT … ON DUPLICATE KEY UPDATE under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, lock acquisition process, and why the same exclusive record lock is obtained in both cases.
This article demonstrates how to use a PHP interface to retrieve data from a MySQL database and employ the ECharts library to create dynamic visual statistical charts, guiding developers through preparation, data extraction, and chart generation steps, including code examples for a bar chart.
This article explores seven practical approaches to generating globally unique identifiers in distributed environments, covering database‑level configurations, MySQL auto‑increment tweaks, MyCat + Zookeeper integration, UUID limitations, Snowflake algorithm implementation, Meituan's Leaf service, Redis counters, and Zookeeper sequential nodes, providing code snippets and configuration guidance.
A developer’s costly mistake using MySQL’s INSERT INTO SELECT for data migration led to massive OOM errors, a full‑table scan, lost payment records, and ultimately a $10 000 loss and termination, highlighting the need for proper indexing and careful testing.
This article explains how database administrators can monitor system resources with commands such as top, iostat, and vmstat, and how to enable and inspect MySQL slow query logs, while also including promotional information about a ChatGPT community and related services.
WeBlog is a Spring Boot and Vue 3.2 based full‑stack blog that combines a MySQL‑backed backend with JWT authentication, MinIO storage, and a hand‑crafted Element Plus admin UI, offering markdown publishing, analytics dashboards, and open‑source repository links for developers to explore.
This article compares MySQL and PostgreSQL, outlining their histories, architectural differences, benchmark results for SELECT, INSERT, UPDATE operations, and discusses suitable scenarios, advantages, disadvantages, and guidance on selecting the appropriate database for various application needs.
This guide presents essential MySQL configuration and monitoring queries—covering connection limits, Binlog/GTID status, InnoDB settings—plus a one‑click script that consolidates these checks, enabling quick health assessments and more efficient routine inspections of MySQL servers.
This article presents a comprehensive engineering guide for importing one billion 1 KB unstructured log records stored in HDFS or S3 into MySQL, covering data sizing, B‑tree limits, batch insertion strategies, storage‑engine choices, sharding, file‑reading techniques, concurrency control, and reliable task coordination using Redis, Redisson, and Zookeeper.
Learn how to deploy stateful databases such as MySQL, PostgreSQL, and MongoDB on Kubernetes by mastering StatefulSets, PersistentVolumes, PVCs, StorageClasses, and related best practices for storage, scaling, backup, monitoring, and disaster recovery.
MySQL 8 removes the VALUES(col) function, requiring developers to use an alias with INSERT INTO ... VALUES (...) AS alias and to reference columns via alias.col in ON DUPLICATE KEY UPDATE, while also changing AUTO_INCREMENT and auto‑update column behaviors.
This article reproduces a MySQL 8.0.32 InnoDB deadlock using two concurrent DELETE statements on the same secondary index, explains the lock types and lock‑wait graph, and details why the deadlock occurs and how it is resolved.
This article compares UUID v4 and UUID v7 as clustered index keys, explains their structures, lists pros and cons, describes a Docker‑based MySQL experiment using Node.js and Go, presents single‑ and multi‑thread insertion timings, and analyses why the time‑ordered UUID v7 (and serial IDs) outperform random UUID v4.
This article compares four pagination techniques—MySQL LIMIT OFFSET, LIMIT with primary‑key filtering, HasMore scrolling, and Elasticsearch pagination—explaining their implementations, advantages, drawbacks such as deep‑page performance issues, and recommending appropriate scenarios for each method.
Learn how to schedule automated MySQL backups for a Docker container using Linux crontab, create optimized backup scripts, compress and retain backups, and recover lost data with binlog files, including detailed command examples and best practices for reliable database maintenance.
This tutorial explains how to design a MySQL table for hot‑search records, generate Java entity and mapper classes using MyBatis Generator, create unique IDs for each entry, and implement scheduled Java crawlers for Douyin and Baidu hot‑search data that persist the results via Spring‑Boot services.
This article provides step-by-step Bash scripts for automating daily MySQL database backups, compressing tables, cleaning old archives, and configuring master‑master replication, including detailed commands for dumping databases, creating tar.gz packages, and setting up replication parameters on Linux servers.
This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+‑tree fan‑out, showing that a table with 100 million rows can still query quickly when the index depth remains low.
This article explains how databases handle insert operations, compares single‑row and batch inserts, discusses factors such as cache‑to‑disk synchronization, transaction logs, page size, hardware limits, and provides practical MyBatis examples for optimizing bulk data loading.
This article details a MySQL 8.0.35 crash caused by an InnoDB assertion that a VARCHAR length exceeds 65,535 bytes, explains the error‑log and core‑dump analysis, shows how to reproduce the bug, and notes its resolution in MySQL 8.0.35.
This guide provides step‑by‑step shell scripts, directory setups, cron jobs, and cleanup commands for reliable backup and restore of Oracle (RMAN and logical), MySQL (full, incremental, Percona XtraBackup), PostgreSQL (logical and physical with pg_rman), and openGauss databases, including retention policies and automation tips.
To avoid accidental full‑table UPDATE or DELETE operations, enable MySQL’s sql_safe_updates (session or global), use MyBatis‑Plus’s IllegalSQLInnerInterceptor, employ IDE plugins and code reviews, and always write explicit WHERE clauses or safe‑update patterns.
This article explains why MySQL auto‑increment IDs can run out in large or special‑case tables and presents six practical solutions—including changing the column type to BIGINT, using UUIDs, segmenting IDs, composite keys, adjusting auto‑increment steps, and sharding databases—to ensure scalable and unique primary keys.
This guide explains why and how to split large MySQL log tables by month, covering table design, core SQL statements, required Webman and ThinkORM setup, and complete PHP code for creating, querying, and inserting into monthly partitions.
This article presents two Bash scripts that automate MySQL multi‑database backups with per‑table dumps and rotate Nginx access logs daily by moving them into month‑based directories and signaling Nginx to reopen its logs.
This article explains how to use PHP's mysqli_query function to perform MySQL SELECT, INSERT, UPDATE, and DELETE operations, provides a complete example script with connection handling, query execution, result processing, and highlights related functions for fetching data.
This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.
This article demonstrates how InnoDB acquires and releases row‑level locks for a sample table under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the SQL statements used, the lock types observed in the performance_schema, and the reasons behind the observed locking patterns.
This article examines a real‑world MySQL deadlock incident, explains the inner workings of shared, exclusive, gap, next‑key, and insert‑intention locks, and shows how concurrent delete‑then‑insert operations can saturate connection pools and degrade overall database performance.
This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.
This article details the end‑to‑end architecture of a high‑traffic business middle platform, covering business and technical design, Kong API Gateway, SpringBoot microservices, Dockerfile creation, MyCat‑MySQL sharding, cold/hot data strategies, DevOps pipelines, Kubernetes deployment, and operational monitoring.
This article explains why MySQL master‑slave replication can become delayed in high‑traffic order systems, describes the underlying binlog synchronization mechanism, and provides practical steps—including network upgrades, server tuning, transaction reduction, version updates, and slave count limits—to eliminate the lag.
This article combines a Huawei campus recruitment overview—including interview timeline, salary grades, and interview process—with extensive technical Q&A covering Java vs C differences, Java features, OOP concepts, common design patterns, Redis and MySQL comparisons, MySQL transaction properties, B‑Tree indexes, list deduplication, stack implementations, and the low‑level workings of the ping command.
This article details how a Java‑based XML‑to‑MySQL import of over 60,000 records was accelerated from 300 seconds to just 4 seconds by enabling JDBC batch processing, configuring rewriteBatchedStatements, and employing a multithreaded Disruptor pipeline for asynchronous writes, while also discussing further tuning options.
This article presents a comprehensive guide for Java engineers on MySQL query optimization, covering index usage, avoiding SELECT *, proper JOINs, efficient WHERE clauses, LIMIT, EXISTS vs IN, function indexes, connection pooling, batch processing, data types, execution plan analysis, materialized views, and regular performance monitoring, all illustrated with practical code examples.
MySQL Innovation Edition 9.1.0, released on October 15 2024, introduces atomic DDL operations, enhanced audit and firewall handling, numerous compiler and SQL function fixes, JavaScript stored‑procedure improvements, new vector support, updated keyring security, pluggable authentication fixes, expanded GROUP REPLICATION logging, and several EXPLAIN and performance‑schema enhancements.
MySQL 9.1.0, released on October 15, 2024, introduces DDL atomicity, improved audit handling, new JavaScript defaults, VECTOR type support, enhanced GROUP REPLICATION logging, expanded EXPLAIN output, and numerous bug fixes across SQL syntax, performance, and security, while the 8.4.3 LTS patch remains feature‑free.
The article explains why network protocols use layered design, details TCP's reliability and congestion‑control mechanisms, outlines HTTP status‑code categories, reviews MySQL lock scopes from global to row‑level, provides a C++ breadth‑first binary‑tree traversal example, and describes a three‑step weighing puzzle to find an odd ball.
This article explains MySQL's logical architecture, detailing the four main layers—connection, core service, storage engine, and data storage—while providing code examples and insights into each component's role in the widely used open‑source relational database.
This article demonstrates how InnoDB handles row, next‑key, and gap locks for a SELECT … FOR SHARE query under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, SQL examples, lock inspection queries, and a concise summary of the differences.
Updating billions of rows in a MySQL table can overwhelm binlog replication and cause deep‑pagination inefficiencies, so this article explains the pitfalls of direct UPDATE, explores limit‑based and IN‑based approaches, and presents a production‑ready batch update strategy using NO_CACHE and forced primary‑key indexing.
This article breaks down a sophisticated SQL script used to export manually added contact data, explaining the involved tables, the construction of the @abc variable, the two UNIONed sub‑queries, and the final selection logic with detailed code examples.
This article presents practical MySQL performance tuning methods, covering LIMIT pagination, implicit type conversion pitfalls, rewriting joins for updates/deletes, mixed-order sorting, replacing EXISTS with joins, predicate pushdown, early result limiting, and intermediate result pushdown, each illustrated with SQL examples and execution plan analyses.
This article explains mock technology for software testing, demonstrates how to implement a mock service using Flask and MySQL, and provides code examples for setting up mock data and handling requests.
This article examines MySQL's AUTO_INCREMENT integer types, comparing the 4‑byte INT and 8‑byte BIGINT limits, calculating how long each can store records under realistic insertion rates, and providing step‑by‑step SQL commands and safety tips for converting INT columns to BIGINT.
The article explains why UUIDv4 primary keys hurt performance and storage, then demonstrates how to replace them in a Spring Boot 3.3 application with a custom Snowflake‑based 64‑bit ID generator, providing full Maven setup, configuration, entity, service, REST controller, and Thymeleaf UI example.
This article introduces CGDB, explains how to choose the appropriate GDB version, provides step‑by‑step installation commands for both CGDB and GDB, and demonstrates practical debugging scenarios with MySQL, including attaching to processes, inspecting threads, analyzing core dumps, and adjusting runtime variables.
This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, large‑table bottlenecks, and read/write splitting—then shows how ElasticSearch’s inverted index and HBase’s column‑family design can complement MySQL for faster search and scalable storage.
Learn how to protect sensitive information in MySQL by installing the open‑source Percona data_masking plugin, verifying its status, and using built‑in and custom masking functions for IDs, phone numbers, emails, names, amounts, and addresses, with step‑by‑step SQL examples.
This article examines the compatibility landscape of major Chinese database products, comparing their support for Oracle, MySQL, PostgreSQL and other standards, and outlines the available assessment and migration tools to help users choose the right solution for heterogeneous environments.
This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.
This article compiles comprehensive MySQL best‑practice guidelines covering charset selection, storage engine choice, naming conventions, field design, index optimization, SQL development tips, and operational procedures to improve performance, maintainability, and reliability of database systems.
This article explains how to clean up a rapidly growing MySQL log table by batching deletions, handling unindexed type columns, and using a start‑id based pagination strategy that scales from tens of millions to billions of rows without locking the database.
This guide details a step‑by‑step MySQL performance optimization strategy covering hardware selection, disk I/O planning, operating‑system tweaks, vertical and horizontal sharding, my.cnf parameter tuning, query cache configuration, resource limits, table and index caching, and practical query‑level best practices.
This article explains how MySQL Router 8.2 enables automatic read/write splitting for InnoDB ReplicaSet deployments, showing configuration steps, code examples, and the benefits of directing reads to replicas and writes to the primary without modifying application code.
This guide walks through five practical MySQL backup and restoration workflows—including scheduled full backups, hourly incremental binlog backups for high‑frequency trading, development‑environment data masking, selective table dumps, and cold‑storage disaster recovery—providing exact commands, configuration tweaks, and step‑by‑step procedures.
This article provides a comprehensive overview of MySQL 8.0's concurrency control mechanisms, detailing table‑level MDL locks, server‑ and engine‑layer table locks, page‑level B+Tree locking, and row‑level lock types with code examples and real‑world deadlock scenarios.
This article investigates the theoretical maximum number of records a MySQL SELECT can return by examining max_allowed_packet, row‑size limits, column count, page‑size constraints, row‑overflow mechanisms, and practical experiments that reveal how these factors together bound query results.
This article demonstrates how InnoDB acquires different types of row and gap locks under REPEATABLE-READ and READ-COMMITTED isolation levels by creating a test table, inserting data, setting transaction isolation, executing SELECT ... FOR SHARE statements, and inspecting lock information from performance_schema.
When initializing a MySQL 5.7.44 instance, the server aborts because the configuration file lacks a trailing newline, causing the parser to misinterpret the final '!includedir' line and report a missing '/etc/my.cnf.' directory, which can be resolved by adding a newline at the end of the file.
This article surveys the compatibility of major Chinese database products with Oracle, MySQL, PostgreSQL and other systems, covering compatibility targets, levels, assessment methods, migration tools, and specific feature support such as protocols, data types, character sets, SQL syntax, procedural languages, built‑in functions, system views, optimizer capabilities and security features.
The article explains that storing IPv4 addresses as a 32‑bit UNSIGNED INT in MySQL saves storage and index space, enables faster range queries, outlines conversion functions like INET_ATON/INET_NTOA, discusses drawbacks such as readability, and provides Java utilities for IP‑long transformations.
This article explains MySQL's layered architecture, how the optimizer generates multiple execution plans, evaluates their I/O and engine costs, and selects the plan with the lowest total cost, illustrated with example queries, cost tables, and JSON output.
The article describes a year‑long development experience where a senior architect designs, plans, and implements a sharding solution for loan and repayment tables using ShardingSphere, Spring Boot 3, MySQL, and custom synchronization code to migrate billions of rows while ensuring data consistency and operational continuity.
The article demonstrates how MySQL Router 8.2 enables transparent read/write splitting for an InnoDB ReplicaSet, showing the architecture, configuration steps, connection behavior, and the generated routing settings that improve database performance without modifying application code.
This article walks through evaluating massive MySQL tables, explains why large row counts slow queries, and compares three practical solutions—table partitioning, database sharding, and hot/cold archiving—while highlighting their trade‑offs and offering guidance on selecting the right approach for a given workload.
The article outlines MySQL’s layered architecture—connector, parser, optimizer, executor, and binary log in the server layer and pluggable storage engines like InnoDB—then walks through the step‑by‑step processing of SELECT and UPDATE statements, highlighting permission checks, caching, plan selection, redo and binlog handling for crash‑safe durability.
To efficiently purge old user‑operation logs while retaining the most recent three months and preserving type‑c entries, the article recommends abandoning offset pagination and MyBatis PageHelper, using primary‑key‑based pagination with a tracked startId, in‑memory filtering, intelligent startId recalculation, and optional sleep intervals to avoid database overload.
The article explains why executing a full‑table UPDATE on large MySQL tables using row‑based binlog can overload replication, discusses the inefficiencies of deep pagination and IN clauses, and presents a batch‑processing strategy that leverages SQL_NO_CACHE and FORCE INDEX to safely and efficiently migrate data.
This article explains how adding an index to a status column in a tens‑of‑millions‑row MySQL table affects query performance, covering the basic index lookup process, extreme cases where full scans are chosen, selectivity, covering indexes, composite indexes, partitioning, and using EXPLAIN to verify execution plans.
This guide explains why MySQL or Java processes can consume 700‑900% CPU in production, walks through step‑by‑step diagnosis using Linux tools, and provides concrete remediation techniques such as indexing, caching, thread analysis, and code adjustments to restore normal performance.
When a MySQL 5.7 instance crashes without backups or redundancy, this guide shows how to recover both table structures and data using the open‑source tools dbsake and ibd2sql, providing exact commands, parameter explanations, and practical tips for a successful rescue.
This article explains MySQL storage engine concepts, compares InnoDB, MyISAM, and MEMORY, describes how data is stored in shared tablespace versus file‑per‑table modes, and provides configuration commands for switching modes, changing default engines, and inspecting or altering a table’s engine.
This guide walks through recovering a deleted MySQL database stored in Docker volumes by locating the mysql.ibd file, recreating a container with the same image, and mounting the original volume, allowing the lost data to be restored without a backup.
This comprehensive guide explains MySQL indexing fundamentals, B+Tree advantages, index classifications, optimal creation strategies, and how to use EXPLAIN to diagnose and fine‑tune queries for maximum performance while avoiding common pitfalls.
This article demonstrates how to encapsulate MySQL operations in a Python MySQLWrapper class, providing modular methods for connecting, querying, inserting, updating, and deleting records, along with a complete example and usage instructions.
This guide walks through verifying MySQL 8.0.16+, copying and editing the mysqld.service file, setting the Service Type to notify, adding NotifyAccess, adjusting ExecStart, reloading systemd, restarting MySQL, and checking its status, while covering SELinux, firewall, and notification‑delay optimizations.
