This article explains why MySQL auto‑increment primary keys can become non‑continuous, covering storage mechanisms, unique‑index violations, transaction rollbacks, batch inserts, and step‑size settings, and provides practical examples and diagrams to help developers diagnose and avoid ID gaps.
The article introduces X‑Stor, a cloud‑native NoSQL database service that supports multiple data models and process‑level multi‑tenant isolation, describes its plugin‑based architecture and Request Unit metric, and presents experimental results showing superior performance, effective tenant isolation, and load‑balancing benefits compared with traditional single‑model NoSQL systems.
This article explains how MySQL uses B‑tree‑based indexes, covering balanced binary trees, B‑trees, B+‑trees, various index types, data‑type effects, and best practices for creating and using composite indexes to optimize query performance.
The article reviews MySQL's 29‑year evolution, detailing the 8.0 release’s extensive contributions, new features, bug‑fix statistics, and the vital role of the global community and Oracle’s ongoing investment in both the open‑source edition and the HeatWave cloud service.
StarRocks 3.3.1 introduces a cloud‑native persistent index that moves index files to object storage, eliminates local‑disk constraints, and supports elastic scaling, delivering up to ten‑fold latency improvement over local‑disk indexes in elastic scheduling while matching performance in batch and real‑time imports.
This article explains why Java ORM frameworks are essential for simplifying database access, outlines the critical criteria for selecting the right ORM, compares popular choices such as Hibernate, JPA, EclipseLink, and DataNucleus, and provides a step‑by‑step guide for successful implementation.
The article details how the vivo Game Center’s appointment service migrated billions of rows to a dedicated MySQL database with zero‑downtime by implementing a double‑write strategy via a custom MyBatis interceptor that mirrors updates and queries to the new database, handling full and incremental sync, primary‑key consistency, transaction limits, and final cut‑over steps.
The article shares a graduate's interview experience and provides a comprehensive technical guide covering HTTP/1.x vs HTTP/2 differences, Java socket and HTTP server code, OS I/O buffering vs direct I/O, database concurrency control, Bloom filter principles, Java reflection, annotation usage, and synchronized locking semantics.
This article presents a detailed walkthrough of a MySQL InnoDB deadlock case, covering background, log inspection, data preparation, reproduction steps, lock analysis, root‑cause explanation, and practical solutions to prevent and resolve similar deadlock issues.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, shows how to create and drop full‑text indexes, and demonstrates the three query modes—Natural Language, Boolean, and Query Expansion—along with their syntax, operators, relevance calculation, and practical examples.
This article explains why the OBOracle "set transaction read only" command provides repeatable‑read semantics, evaluates the behavior of the JDBC conn.setReadOnly(true) setting, and shows how to achieve true repeatable‑read isolation by enabling the oracleChangeReadOnlyToRepeatableRead option in the OceanBase client.
This article introduces ReadySet, an open‑source SQL caching layer for MySQL and PostgreSQL, explains its architecture and advantages, and provides step‑by‑step instructions for installing the Docker image, configuring connections, creating caches, and testing query performance to achieve millisecond‑level response times.
This guide walks through checking the OS architecture, preparing the environment, downloading the appropriate MySQL 5.7 packages for ARM, installing and configuring MySQL, setting up automatic startup and environment variables, initializing the server, securing the root account, and finally configuring master‑slave replication between two servers.
This article recounts L's attempt to change a train ticket, analyzes seat availability across four segments, and presents a minimal SQL model with table creation, data insertion, and a query that determines feasible seat combinations, demonstrating how segment‑wise seat allocation can enable complete journey booking.
This article explains how to achieve idempotent order creation and update by using unique order IDs, database primary‑key constraints, Redis flags, and a version‑column mechanism to detect and prevent duplicate submissions and ABA anomalies in distributed systems.
This guide walks you through why Java remains vital, explains what Spring Boot is, outlines its core features, and provides a detailed learning roadmap covering Java fundamentals, Spring core concepts, web development, AOP, microservices, databases, security, DevOps tools, and deployment strategies for building robust enterprise applications.
This article provides a comprehensive guide to using MyBatis dynamic SQL tags—including foreach, if, choose, trim, selectKey, and sql fragments—explaining their attributes, usage scenarios, and offering detailed Java and XML code examples to improve query flexibility and reduce errors.
This tutorial demonstrates how to perform fundamental Redis operations—including setting, retrieving, and deleting keys, working with hashes, lists, and sorted sets—using Python's redis client and includes complete test code for each scenario.
The article details a zero‑downtime migration of a high‑traffic reservation service from a shared MySQL instance to an isolated database by employing a double‑write strategy implemented through a custom MyBatis plugin, covering full and incremental sync, consistency verification, traffic shifting, and key lessons on primary‑key handling and asynchronous write side‑effects.
This article provides a step‑by‑step analysis of MySQL 8.0.32 InnoDB deadlock logs, covering preparation, log extraction, auxiliary information, transaction details, row‑lock structures, and the final summary, with full SQL examples and code snippets for deep understanding.
This guide walks you through preparing multiple virtual machines, configuring MySQL 8.0 server IDs, setting up both classic and GTID‑based master‑slave replication, and managing replication commands, providing clear code snippets and commands for a reliable MySQL clustering environment.
On August 14, 2024, GitHub experienced a massive site-wide outage caused by a database infrastructure configuration change that disrupted traffic routing, leading to loss of database connections and affecting core services such as Pull Requests, Pages, Copilot, and the API, with full restoration confirmed later that evening.
This guide walks through ten common Redis tasks—setting and getting keys, deleting keys, listing keys, hash, list, set, sorted‑set operations, publish/subscribe, pipeline, and transactions—each illustrated with clear Python redis‑py code examples.
This article explains the concept of database connection pools, why they are needed, their core principles and runtime mechanisms, compares traditional direct connections with pooled connections, and reviews major Java pool implementations such as DBCP, C3P0, Druid and HikariCP along with configuration tips and best‑practice considerations.
This article explains the various moments when InnoDB table and row locks are released during a transaction's lifecycle, covering mismatched WHERE conditions, the two‑phase commit prepare stage, AUTO‑INC locks, and differences across isolation levels in MySQL 8.0.32.
MySQL’s pluggable storage‑engine architecture lets each table use a specific engine, with InnoDB (the default in MySQL 8.x) offering row‑level locking, ACID transactions, foreign‑key support, crash recovery, MVCC and a buffer pool, while MyISAM provides only table‑level locking, no transactions, and is suited mainly for read‑intensive workloads lacking recovery requirements.
This article examines the challenges of fuzzy searching encrypted data and compares three implementation approaches—naïve, conventional, and advanced—detailing their principles, performance implications, storage costs, and security trade‑offs, ultimately recommending the conventional token‑based method for most practical applications.
This FAQ explains how Superset handles massive datasets, required compute specs, joining tables, creating custom visualizations, CSV uploads, query timeout settings, map visibility, dashboard refresh control, schema changes, supported database backends, OAuth configuration, color overrides, API usage, telemetry, and asset recovery.
This article describes a business approval scenario where a company name entered by a business user must be checked for duplicates, and explains how to implement fuzzy matching using MySQL RegExp, tokenization with IKAnalyzer, and Java service code to extract, preprocess, match, and rank results by relevance.
This article introduces a Spring‑based asynchronous processing SDK that leverages annotations, transaction event listeners, Kafka, XXL‑Job, and a dedicated database to achieve non‑blocking execution, fault tolerance, and eventual consistency while preserving transaction integrity and providing a clear configuration and usage guide.
This article examines whether a MySQL replica can safely omit setting sync_binlog=1, explains the underlying InnoDB and GTID mechanisms, presents an experiment reproducing power‑loss scenarios, and analyzes how different master_auto_position settings affect data consistency and GTID continuity during recovery.
This article explores the subtle timing of Spring transaction start and commit relative to lock/unlock, demonstrates how improper ordering can cause over‑selling in high‑concurrency scenarios, and provides debugging techniques and practical solutions to ensure atomicity and correct rollback behavior.
This article introduces Easy‑Query, a Java ORM that provides a strongly‑typed, chainable query syntax covering basic CRUD, pagination, joins, sub‑queries, native SQL fragments, group‑by, custom VO mapping and high‑performance encryption, illustrated with extensive code examples and usage notes.
This article provides a comprehensive guide to MySQL's DQL, DML, and DDL sections, explaining execution order, core clauses, and practical code examples for creating, altering, and managing databases and tables.
This article highlights seven world‑class software projects originated by Russian developers—including 7‑Zip, Telegram, Nginx, Kaspersky, JetBrains IDE suite, ClickHouse, and the classic Tetris game—explaining their history, key technologies, and impact on everyday computing.
This article explains what constitutes a slow SQL query, how to detect and analyze it using MySQL tools, and provides practical optimization steps—including indexing, business logic review, caching, scheduling, and partitioning—to dramatically reduce execution time.
This article examines why a scalar subquery in an OceanBase 3.2.3.3 SQL statement takes over 1000 seconds, breaks down the execution plan, identifies the costly nested-loop behavior, and presents a rewrite using WITH and LEFT JOIN that reduces the cost from 788 million to 3.6 million and cuts runtime to about 10 seconds.
MySQL’s TINYINT(1) stores a full byte integer allowing any value from 0‑255 (or –128‑127 signed) and works with arithmetic, offering broad compatibility, while BIT(1) stores a single bit limited to 0 or 1, is MySQL‑specific, and is best used only when true/false storage and minimal space are essential, making TINYINT(1) the generally preferred choice unless strict boolean constraints apply.
This article examines why MySQL pagination using large LIMIT OFFSET values becomes increasingly slow, explains the underlying scanning behavior, and presents six practical optimization techniques—including indexed subqueries, cursor‑based pagination, column selection, partitioning, caching, and hardware considerations—to dramatically improve query performance on massive tables.
This article examines three categories of techniques—naïve, conventional, and advanced—for enabling fuzzy queries on encrypted data, comparing their implementation steps, performance impact, storage overhead, and security trade‑offs, and provides practical examples and reference links for further study.
This article analyzes the problems of scattered property configuration in SpringBoot projects, designs a database table to store configuration items, implements a configuration loader that injects database values into the Spring environment, and demonstrates testing and best‑practice considerations for a robust backend configuration solution.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, including a full example that creates a connection, runs a query, processes results, and closes the connection.
StarRocks combines vectorized execution, a new cost‑based optimizer, materialized views, a real‑time storage engine, pipeline execution, and distributed joins to deliver a unified, high‑performance OLAP solution that supports both traditional and lakehouse analytics while reducing operational complexity.
To avoid common MySQL pitfalls, the article compiles practical SQL writing guidelines—selecting specific columns instead of *, using LIMIT 1, avoiding OR in WHERE, optimizing pagination, handling LIKE patterns, minimizing redundant indexes, preferring INNER JOIN, and leveraging covering indexes and EXPLAIN for efficient query execution.
The article details a real‑world backend engineering project where a loan repayment service is refactored to use table sharding with ShardingSphere, covering design decisions, table naming, sharding algorithms, historical data migration, dynamic switches, transaction management, code examples, and practical pitfalls.
This article explains how to speed up SQL queries while keeping statements concise, covering the query processing workflow, optimization goals, and practical tips such as selecting specific columns, avoiding DISTINCT, using proper indexes, preferring EXISTS over COUNT, limiting result sets, favoring WHERE over HAVING, and replacing correlated subqueries with joins.
This guide explains how to set up JDBC drivers, construct connection URLs, use PreparedStatement instead of Statement, iterate ResultSet safely, manage resources with try‑with‑resources, handle transactions, employ connection pools, and properly process SQLExceptions for robust Java database applications.
This article analyzes why two identical DELETE statements on a MySQL table with a unique index can deadlock, explains InnoDB lock types, walks through the execution flow, reproduces the issue with SQL and Java code, and discusses mitigation strategies.
MySQL 9.0, released July 2 2024, introduces EXPLAIN JSON output stored in variables, native EVENT DDL statements, and two new performance‑schema tables for variable metadata, while deprecating the old variables_info columns and removing the mysql_native_password plugin in favor of caching_sha2_password, enhancing observability and security.
This article explains how to obtain the SQL ID, create an outline bound to that ID, and correctly verify its effect in OceanBase, highlighting common pitfalls such as using the wrong schema or relying on EXPLAIN, and provides practical code examples for each step.
LibraDB, the column‑store replica of TDSQL MySQL, has evolved its execution engine from a simple scatter‑gather model to a vectorized SMP pipeline that integrates MPP parallelism, asynchronous I/O, SIMD‑accelerated aggregation and join operators, work‑stealing, and runtime filters, thereby fully exploiting CPU, memory, network and disk resources for both OLTP and analytical queries.
This guide walks through installing pypika and demonstrates how to construct SELECT, WHERE, GROUP BY, JOIN, subqueries, set operations, INSERT, UPDATE, and database‑specific adaptations using Python code, showcasing concrete examples and the full query‑building workflow.
This article explains the motivation behind creating the Easy-Query ORM for Java, demonstrates its fluent query capabilities—including single‑record, list, pagination, joins, sub‑queries, group‑by, native SQL and dynamic condition features—provides extensive code examples, and shares repository links for further exploration.
This tutorial demonstrates how to set up a simple MySQL database, create a Spring Boot project, add Mybatis-Plus dependencies, install the MybatisX plugin, configure the connection, generate entity, mapper, service, and controller code, and verify the integration with a sample REST endpoint.
This guide presents a thorough MySQL performance checklist, covering efficient SQL writing, data‑type choices, selective column retrieval, smart joins, batch inserts, and query‑cache settings; it details key InnoDB and server variable tuning, proper index creation and usage, common pitfalls, and how to analyze and improve queries with EXPLAIN.
This article provides a comprehensive, hands‑on tutorial for planning, installing, configuring, and verifying a PostgreSQL master‑slave (streaming replication) setup on two Linux nodes, including user creation, remote connection settings, data export/import, and troubleshooting tips.
This article explains how two identical DELETE statements on a table with a unique index can cause a deadlock in MySQL 5.7, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies and behavior changes in MySQL 8.
This article examines why identical DELETE statements on a MySQL table with a unique index can cause deadlocks, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies such as version upgrades, isolation level changes, and distributed locks.
This article presents 17 versatile Python scripts that automate common tasks such as file management, web scraping, email handling, Excel processing, database interaction, system administration, and more, offering code examples and explanations to help developers and analysts improve productivity.
This guide explains how to set up automated MySQL database backups using a shell script that runs mysqldump (optionally compressed), stores the files on a local disk, makes the script executable, and schedules it with a cron job after verifying disk space and cron availability.
MySQL 9.0 Innovation Edition, released on July 1, 2024, introduces major changes such as the removal of the mysql_native_password plugin, new PREPARE support for event statements, expanded Performance Schema tables, JSON‑formatted EXPLAIN ANALYZE output, Windows installer fixes, and several MySQL Shell enhancements.
This article explains how Redis operates primarily with a single‑threaded model for memory and network operations, why this design is efficient, and how recent versions introduce multi‑threaded components for network I/O, persistence, and asynchronous deletion while preserving thread safety.
Learn how to efficiently import large Excel files into a database using POI, JDBC, MyBatis, and Spring Boot, with step-by-step code examples, performance bottlenecks, and optimization techniques such as caching, asynchronous processing, multi-threaded batch inserts, and thread‑pool configuration for maximum throughput.
This article investigates whether MySQL re‑executes an UPDATE statement that sets a column to its current value, comparing behavior under binlog_format=ROW and binlog_format=STATEMENT with binlog_row_image=FULL, and presents test steps, results, and conclusions.
This article explains why organizations may replace traditional relational databases with Apache Kafka as a system of record, highlighting Kafka's economic, scalable, immutable log capabilities, event replay, flexibility for diverse use cases, and its suitability for highly regulated, data‑intensive environments.
This article explains how to design the database, modify product detail, cart, and order pages, and use Ajax in PHP to enforce purchase quantity limits based on stock levels, ensuring accurate inventory management and improved user experience in online stores.
This article analyzes why distributed transactions are difficult in large‑scale microservice architectures, explains the importance of unique transaction IDs, idempotency, and timeout handling, and presents pragmatic approaches such as local logging, undo‑log tables, NoSQL ID arrays, and selective locking to achieve reliable consistency.
This article explains why upgrading MySQL from 5.7 to 8.0 can cause an "Illegal mix of collations" error in FIND_IN_SET queries, analyzes the root cause, and presents three practical solutions—adjusting server parameters, altering table collations, or rewriting the SQL—to restore correct operation.
This article explains the common slow approach of reading Excel files with POI and inserting rows via JDBC, then presents three practical optimizations—caching data, using asynchronous multithreaded reads, and per‑file async processing—along with thread‑pool configuration tips and EasyExcel examples to dramatically reduce import time from minutes to seconds.
This article walks through MyBatis dynamic SQL tags—foreach, concat, choose, selectKey, if, where, trim, set, and sql fragments—explaining their attributes, usage patterns, and common pitfalls, and provides complete XML and Java code examples for each technique.
While MySQL INT and BIGINT auto‑increment columns usually suffice, this article explains their numeric limits, calculates how many records each can store over time (e.g., at 100 inserts per second), and provides practical steps to switch from INT to BIGINT safely.
This tutorial explains how to connect to a MySQL database using PHP's mysqli extension, execute queries with mysqli_query, and retrieve each row with mysqli_fetch_assoc, providing complete code examples and a full script for efficient database handling.
This article explains why replacing MyBatis with MyBatis‑Plus caused a LocalDateTime conversion exception, identifies the root cause as an outdated mysql‑connector‑java version, and shows how upgrading the connector (to 5.1.37 or later) resolves the issue while providing code examples and debugging steps.
The article examines Peter Zaitsev’s critique of Oracle’s stewardship of MySQL, debunks long‑standing myths, evaluates recent MySQL Heatwave features and performance gaps, and compares MySQL’s trajectory with PostgreSQL and other open‑source databases.
This article explains InnoDB lock concepts, differentiates read and write locks, discusses table‑level versus row‑level locking, and examines how MySQL 5.6 and 8.0 handle adding columns without fully locking tables, highlighting online DDL, atomic DDL, and performance considerations.
MySQL offers a comprehensive set of string functions—including lower, upper, concat, substring/substr, length, char_length, instr, lpad, rpad, trim (with leading, trailing, both options), replace, repeat, reverse, and format—that enable case conversion, concatenation, extraction, padding, trimming, substitution, repetition, reversal, and numeric formatting of textual data.
On June 3, 2024 DuckDB announced its 1.0.0 "Snow Duck" release, highlighting a custom single‑file storage format with full transaction semantics, backward compatibility, massive community adoption, and a roadmap focused on stability, extensions, and continued open‑source development.
This article explains how to create two interactive Coze bots—a fish‑knowledge quiz and a nostalgic 147‑258‑369 chess game—by designing workflows, integrating large language models, using databases, and implementing Node.js code for canvas drawing and GIF generation, complete with deployment tips.
MyBatis dynamic SQL lets developers construct conditional queries at runtime using tags like if, where, trim, choose, set, foreach, sql, include, and bind, simplifying complex statements, avoiding manual string concatenation, supporting batch operations, reusable fragments, and one‑to‑many mappings while improving flexibility and maintainability.
This article explains the Paxos consensus algorithm, detailing its roles, three-phase execution process, key properties such as consistency, availability and fault tolerance, and showcases its practical applications in distributed databases, file systems, and coordination services.
This article walks through the complete process of creating an AI‑driven electronic‑pet bot on the Coze platform, covering concept brainstorming, database design, image‑generation pipelines, Python code snippets, multi‑agent workflow orchestration, and final deployment to the bot store.
Ensuring API idempotency is crucial to avoid duplicate operations like double crediting points or double payments, and this article examines common causes of repeated calls—such as rapid user clicks, retries, and message queues—and presents practical backend solutions including frontend controls, Redis setnx, token validation, unique indexes, counting, and state machines.
This article examines why a newly created MySQL user (sky1@%) cannot log in via socket after upgrading from 5.6 to 5.7, demonstrates how the ChatDBA AI assistant diagnoses the issue, proposes a fix by removing an anonymous user, and compares results with ChatGPT‑4o.
NULL, the ubiquitous placeholder for missing values, can silently introduce bugs, crashes, and data inconsistencies across languages and databases, but by understanding its pitfalls and applying strategies like optional types, default values, Null Object patterns, and proper database constraints, developers can significantly improve code reliability.
This article walks through a real‑world Oracle index failure, explains why indexes become UNUSABLE, shows how to clean residual metadata, rebuild the index safely, outlines common causes for both regular and partitioned tables, and provides SQL scripts for monitoring invalid indexes.
Learn how to create a Go-based command-line tool that accepts user-supplied parameters to connect to an Oracle database using the loginWithServiceName function, execute arbitrary SQL queries, and display results, complete with full source code, flag parsing, and error handling.
This guide explains how to configure and run a Workerman‑based PHP forward proxy that forwards MySQL connections, enabling smooth access to internal databases via Navicat Premium or other clients, with detailed code snippets, startup commands, and Windows usage instructions.
This article evaluates MySQL single‑thread write performance by testing three insertion methods—continuous while‑loop inserts, JDBC batch processing, and multi‑row INSERT statements—detailing setup, code examples, and measured rows per minute to reveal their relative throughput and stability.
This article details the product models, usage scenarios, and a series of performance and operational optimizations—including query plan health checks, space reclamation, resource isolation, cloud‑native deployment, cross‑region high availability, and unified monitoring—implemented for the TiDB service operated by 360 Zhihui Cloud since its launch in April 2023.
This guide explains how to download and install the Redis extension, add the Predis package to a Laravel project, configure single‑node and clustered Redis connections in config/database.php, and demonstrates basic Redis commands, transactions, and Lua scripting within Laravel controllers.
This article explains why MySQL’s built‑in utf8 charset only supports three‑byte characters, why emojis cause errors, and how switching to the proper utf8mb4 charset resolves insertion issues while preserving full Unicode support.
This tutorial explains how Redis Cluster automatically shards data across multiple nodes, covering required TCP ports, hash‑slot sharding, master‑slave replication, consistency trade‑offs, essential configuration parameters, and step‑by‑step commands for creating, expanding, resharding, and managing a production‑grade Redis cluster.
This article walks through a real‑world approval workflow where a business user adds a company, explains why MySQL REGEXP is chosen over Elasticsearch for fuzzy matching, and details the step‑by‑step implementation—including name preprocessing, tokenization with IKAnalyzer, and a custom SQL query that ranks results by match degree.
This tutorial walks through building a PostgreSQL high‑availability cluster using Pacemaker and Corosync, covering architecture overview, component installation, cluster status checks, data synchronization verification, failover handling, and common maintenance commands with concrete commands and screenshots.
This article demonstrates how to diagnose and resolve repeated UPDATE failures in OceanBase by extracting information from Top SQL, log files, and the gv$sql_audit view, identifying error codes such as 6005, 6003, and 6212, and applying corrective actions like adjusting timeout settings, killing lock‑holding sessions, and checking lock statistics.
This article explains the essential conditions, metrics, and scenario classifications for building high‑concurrency systems, then details common solutions such as database read/write separation, local and distributed caching, cache‑eviction policies, handling master‑slave lag, preventing cache penetration and avalanche, and applying CQRS to achieve scalable, high‑performance back‑end services.
This article provides a comprehensive step‑by‑step guide to setting up MongoDB replica sets and sharded clusters, explaining the architecture, member roles, configuration files, initialization commands, and operational procedures for ensuring data redundancy, high availability, and horizontal scaling.
This article demonstrates how to refactor a complex, production‑level SQL query used in a routing system by formatting, decomposing into layers, merging temporary tables, pushing predicates, optimizing joins, and validating performance, ultimately reducing nesting from four levels to one and cutting execution time from 4.75 s to 0.6 s.
This article explains how to connect to a MySQL database with PHP, execute a SELECT query, and use the mysqli_fetch_assoc function to fetch rows as associative arrays, including code examples and best‑practice notes for handling multiple rows.
A MySQL‑driven data‑migration using INSERT INTO SELECT caused full‑table scans, lock contention and lost payment records, leading to a $10,000 loss and the engineer’s dismissal; the post‑mortem explains the root cause and how proper indexing can prevent it.
