This article explains how MySQL treats backslashes in INSERT and SELECT statements, demonstrates the resulting storage differences with practical examples, and clarifies the double‑escaping rules that developers must handle to avoid unexpected query results.
This article provides a detailed, step‑by‑step tutorial on installing, configuring, and using JetBrains DataGrip—including connection setup, SQL editing, result navigation, data import/export, and advanced editor shortcuts—demonstrating why the tool is considered a powerful and user‑friendly database client.
This article examines the challenges of using Kafka, Flink, and Hive for real‑time data warehousing, introduces Apache Iceberg 0.11 as a solution, details its architecture, query planning, Flink integration, code examples, optimization techniques, and summarizes the benefits for large‑scale data processing.
Java developers often mishandle SQL, leading to performance, correctness, and maintenance issues; this article outlines ten common pitfalls—from misunderstanding NULL and misusing UNION to neglecting MERGE and batch inserts—and provides practical solutions to write efficient, reliable database queries.
This article provides a comprehensive collection of practical MySQL and SQL performance optimization techniques, covering index usage, query rewriting, avoiding full table scans, proper data types, efficient joins, backup strategies, and other best practices to improve database speed and reliability.
This article explains the special nature of NULL in SQL, how to correctly test for NULL using IS NULL, the three‑valued logic of true/false/unknown, and the pitfalls of NOT, NOT IN, and Oracle’s handling of empty strings versus NULL.
This article walks through the challenges of implementing a multi‑criteria product search for a shopping site, compares a naïve SQL solution with optimized query splitting, and then shows how Redis sets, sorted sets, and transactions can dramatically improve performance while adding pagination and update handling.
This article provides an in‑depth overview of Presto, covering its history, core principles, architectural components, query optimization techniques, resource management, tuning tips, data model, and case studies from companies like Didi and Youzan, offering practical guidance for deploying and operating the distributed SQL engine at scale.
This article provides a step‑by‑step walkthrough of installing DataGrip, configuring data sources, using its SQL console, creating tables, exporting and importing data, and leveraging powerful navigation and editor shortcuts to boost database productivity.
This article examines how misplaced quotation marks in MySQL UPDATE statements can unintentionally set fields to zero, explains MySQL’s implicit type conversion that causes the condition to always evaluate true, and offers practical steps for detecting and preventing such bugs.
A developer’s accidental misplacement of quotation marks in UPDATE statements caused all target fields to become zero, and this article explains the MySQL parsing behavior, implicit type conversion, and how to prevent such silent data corruption.
This article examines the pitfalls of reusing existing SQL stored procedures to determine the presence of Job, Certification, and Disclosure data for a user, illustrating maintenance challenges with temporary tables, output parameters, and code‑reuse limitations through concrete code examples.
Starting with MySQL 8.0.23, columns can be marked INVISIBLE, causing them to be omitted from SELECT * queries unless explicitly referenced; this article explains the feature, demonstrates creation, inspection, DML considerations, schema modifications, backup behavior, and its impact on database design.
The article outlines ten frequent errors that Java programmers encounter when writing SQL—ranging from misunderstanding NULL handling to misusing UNION, pagination, and batch inserts—and provides practical solutions to improve correctness, performance, and maintainability of database interactions.
This article outlines ten frequent errors Java programmers encounter when writing SQL, explains why each mistake occurs, and provides practical solutions to improve correctness, performance, and maintainability of database interactions in Java applications.
This article examines the trade‑offs between automatically generated and manually crafted SQL parsers, detailing lexical‑syntax analysis techniques, performance challenges in OLAP workloads, and the authors’ custom parser design that achieves up to 30‑50× speed gains in complex queries and bulk inserts.
This article reviews the history of MySQL and MariaDB, describes a test environment, runs insertion, batch, and query benchmarks (with and without indexes), and concludes that MariaDB generally outperforms MySQL in speed and memory usage while highlighting trade‑offs of indexing.
This tutorial introduces pandasql, a compact Python library that enables SQL queries on pandas DataFrames within the Rodeo IDE, covering installation, built‑in datasets, basic queries, aggregation, joins, and visualization tips for data‑analysis beginners.
This article provides a detailed tutorial on using MyBatis-Plus in Java, covering setup, entity and mapper definitions, CRUD operations, annotations, condition builders, pagination, AR mode, logical deletion, automatic field filling, optimistic locking, performance analysis, multi‑tenant support, dynamic table names, configuration options, and code generation.
This article provides a thorough overview of relational database fundamentals, SQL syntax structures, data definition and manipulation languages, transaction control, permission management, as well as detailed examples of CRUD operations, subqueries, joins, unions, functions, constraints, stored procedures, cursors, and triggers, all illustrated with practical code snippets.
This article explains how 58's commercial data team built a comprehensive data metric system—from identifying common metric definition issues to establishing a domain‑driven hierarchy, distinguishing atomic and derived metrics, implementing a unified metric management platform, and providing APIs and examples for querying and visualizing metrics.
The article examines a production MySQL query that suffers severe slowdown due to large LIMIT offsets, demonstrates how to reproduce the issue with massive test data, analyzes the root cause, and presents three optimization strategies—including index covering, keyset pagination, and offset limiting—to dramatically improve query performance.
This article introduces the fundamentals of distributed database architectures, compares Shared‑Nothing and Shared‑Storage designs, explains their three‑tier structure, core engine components, SQL execution flow, performance and cost optimizations, and showcases a real‑world high‑traffic deployment in Douyin’s Spring Festival event.
This article explains how incorrect placement of quotation marks in MySQL UPDATE statements can cause the SET values to be evaluated as boolean expressions, leading to implicit type conversion that updates fields to zero, and offers practical steps to prevent such errors.
An engineer describes a production MySQL pagination issue where large offset queries caused severe slowdown, analyzes the root cause, demonstrates data simulation scripts, and presents three optimization strategies—including index covering with subqueries, redefining start positions, and applying offset limits—to improve query performance on massive tables.
This article provides a comprehensive overview of data warehouses, explaining their purpose, differences from databases, OLTP vs OLAP, traditional versus internet data warehouse models, layered architecture, modeling theories, metric dictionaries, date dimensions, naming conventions, data governance, and incremental synchronization techniques with practical SQL examples.
The article explains PHP's PDO::exec method, detailing its signature, parameters, return value, and provides a complete example showing how to delete rows from a database table and retrieve the count of affected rows.
This article demonstrates how PostgreSQL 14's pageinspect extension adds functions to examine GIST index internal pages, provides step‑by‑step SQL examples for creating a test table and index, and shows how to query opaque page info and item data using the new functions.
The PDO::__construct method creates a PDO object that represents a connection to a database, detailing its parameters—DSN, username, password, and driver options—return value, and providing a complete PHP example of establishing and handling a MySQL connection.
This article examines common MySQL performance pitfalls such as inefficient LIMIT offsets, implicit type conversions, suboptimal UPDATE/DELETE joins, mixed sorting, misuse of EXISTS, and limited condition pushdown, then demonstrates concrete rewrites—including range reduction and WITH clauses—that reduce execution times from seconds to milliseconds.
This article explains MySQL's three time types, highlights the quirks and automatic defaults of TIMESTAMP—including the 2038 problem, high‑concurrency issues, and unexpected behavior in non‑strict mode—provides practical examples, and offers guidance on configuring server variables or switching to DATETIME for safer handling.
The article explains the functional differences and performance characteristics of MySQL's count(1), count(*), and count(column) expressions, showing when each is appropriate and providing a concrete example with query results to illustrate their behavior.
This article explains the concept of database transactions, the ACID properties, MySQL's four isolation levels, the phenomena they cause such as dirty reads, non‑repeatable reads and phantom reads, and provides step‑by‑step experiments demonstrating each isolation level in practice.
Flink SQL's idle state retention time feature prevents state explosion by automatically cleaning up state for keys that remain inactive beyond a configurable time window, requiring both minimum and maximum retention settings, with implementation details involving CleanupState, timers, and KeyedProcessFunctionWithCleanupState.
This article presents Qunar's practical experience with Flink and Iceberg 0.11, covering background challenges such as Kafka data loss and Hive metadata pressure, explaining Iceberg architecture, query planning, and detailed solutions including real‑time ingestion, small‑file handling, sorting, and code examples for seamless migration.
A developer’s production fix using dozens of UPDATE statements unintentionally set a column to zero because of misplaced quotation marks, illustrating how MySQL’s implicit conversion and syntax quirks can turn seemingly correct SQL into disastrous data loss.
This article explains how LEFT JOIN always returns all rows from the left table, how conditions placed in the ON clause affect only the right‑table matching without filtering left‑table rows, and how a WHERE clause filters the final result, illustrated with multiple SQL examples.
This guide presents a detailed MySQL inspection checklist covering operating‑system metrics, critical MySQL parameters, status queries, replication health, high‑availability components, and useful SQL scripts, enabling DBAs to efficiently monitor performance, detect issues, and maintain reliable database services.
This article explains the concepts of isolation levels, dirty reads, phantom rows, and how various relational and NoSQL databases implement these phenomena, providing practical guidance on choosing the appropriate isolation level to ensure data consistency and performance.
The article shares a developer's post‑interview reflections covering self‑introduction, Spring ecosystem usage, Nacos vs Eureka service discovery, SpringBoot startup mechanics, design patterns, Linux commands, Redis operations, Excel POI handling, SQL optimization techniques, and other practical interview questions.
This article explains MySQL full‑text indexing, compares its syntax with ordinary SQL, demonstrates how to create and query a full‑text index using natural language, boolean, and query‑expansion modes, and shows performance differences through execution‑plan analysis and relevance ranking.
The article investigates the Alibaba Java development guideline that forbids joining more than three tables by designing MySQL and Oracle experiments, generating massive test data, measuring query performance, analyzing index usage, and concluding why the rule exists and when it can be relaxed.
The article explains how incorrectly placed double‑quotation marks in MySQL UPDATE statements change the intended string assignment into a boolean comparison that evaluates to 0, leading to all affected rows being updated with zero values, and shows how to detect and avoid this pitfall.
This article presents a detailed performance benchmark of MariaDB and MySQL, covering single‑row and batch inserts, as well as various query scenarios with and without indexes, and concludes that MariaDB generally outperforms MySQL while using more memory.
This extensive article introduces Hive as a Hadoop‑based data warehouse, explains its architecture, core concepts, DDL/DML syntax, functions, performance‑optimization techniques, data‑skew handling, and provides a collection of common interview questions for Hive practitioners.
The article explains why Java 8 Stream API is still valuable despite SQL offering similar functions, detailing Stream creation, terminal and intermediate operations, collectors, matching, and practical scenarios where Streams simplify data processing across microservices.
This article explains how to resolve incompatibility between SyBase's default datetime export format and MySQL's datetime format by using the convert() function to reformat dates and str_replace() to adjust delimiters, then exporting the data via bcp for seamless import.
This article explains how MySQL 8.0's WITH clause can be combined with INSERT, UPDATE, DELETE, recursive common table expressions, multiple CTEs and derived tables to generate data, create date sequences, and simplify everyday SQL programming with practical code examples.
This article explains how to enable and configure MySQL's Federated storage engine to access remote tables without data synchronization, covering engine architecture, supported features, environment setup, server configuration, table creation, data manipulation, and binlog verification, along with practical code examples.
This article explains the special nature of NULL in relational databases, demonstrates the correct use of IS NULL versus = NULL, explores three-valued logic, and clarifies how NULL interacts with NOT, NOT IN, and empty strings across different database systems.
This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.
This article explains the MySQL password expiration mechanism, introduces the relevant columns in the mysql.user table, demonstrates how to set passwords to never expire, expire after a specific interval, or expire immediately, and shows how to configure the global default_password_lifetime variable.
This article explains why MySQL deadlocks occur in a test module, details table and row lock mechanisms, reproduces the deadlock scenario with two sessions, analyzes lock logs, and provides a solution by adding a composite index to prevent the conflict.
This article explains why using SELECT * in MySQL queries hurts performance, explores the underlying reasons such as extra data transfer, I/O overhead, and loss of covering index optimization, and provides practical guidance on index usage and best practices.
The article details how Alibaba’s Data Bank leverages AnalyticDB’s cold‑hot tiered storage, high‑throughput real‑time writes, and low‑latency OLAP capabilities to handle petabyte‑scale consumer data, support flexible AIPL analysis, crowd profiling, and rapid audience selection while cutting costs and ensuring elasticity during peak events.
This article explains what NoSQL means, contrasts it with SQL and relational databases, walks through schema design, primary keys, indexing, and then demonstrates how to implement a lightweight key‑value NoSQL store in pure Python with full command parsing and handling.
This article explains the hidden costs of using SELECT * in MySQL—such as extra data transfer, increased I/O, and loss of covering‑index optimization—while detailing how proper column selection and composite indexes can dramatically boost query efficiency.
The article explains why adding conditions after a LEFT JOIN using AND does not filter rows from the left table, contrasts ON and WHERE clauses, and demonstrates the behavior with SQL examples and step‑by‑step illustrations of intermediate result tables.
This article explains MySQL full-text (inverted) indexes, their underlying auxiliary tables, buffer pool configuration, document ID handling, and the special transaction semantics required for inserting and querying indexed data, providing practical SQL examples and performance considerations.
This article analyzes six MySQL pagination techniques, presents benchmark results showing how query time grows with offset, and offers practical index‑based optimizations—including covering indexes, subqueries, and composite index design—to make LIMIT pagination fast even on very large tables.
This article explains MySQL index fundamentals, covering primary and unique indexes, best practices for choosing indexed columns, handling functions and large fields, avoiding excessive or frequently updated indexes, and enumerates typical scenarios where indexes become ineffective.
This article explains how LEFT JOIN works in SQL, clarifying that the ON clause only matches rows without filtering left‑table records while the WHERE clause filters the joined result, and demonstrates the differences with multiple code examples and table illustrations.
This article explains how MySQL's sys.diagnostics stored procedure collects performance metrics, the information it reports, required permissions, command examples, configurable parameters, and how to generate and interpret diagnostic reports for effective database analysis.
This article examines why full-table scans become slow on large MySQL tables, demonstrates various pagination techniques—including simple LIMIT, sub‑query, ID‑range, and IN‑clause methods—provides benchmark results for different record counts and offsets, and offers practical tips to dramatically speed up paginated queries.
This tutorial demonstrates how to use Spark SQL to create, insert, update, delete, merge, and drop Apache Hudi tables, covering environment setup, Spark‑SQL launch, configuration, and a series of SQL commands with example outputs.
This article explains MySQL’s internal architecture—including server and storage engine layers—and walks through each stage of query processing from connection and authentication, through query cache, parsing, optimization, and execution, illustrated with a concrete SELECT example.
This article explains how MySQL maintains primary and composite (non‑primary) indexes using B+ trees, outlines the essential equality‑matching, left‑most‑prefix, and range‑scan principles, and shows how to combine them with ORDER BY, LIMIT, and GROUP BY for optimal query performance.
This article presents Kuaishou's comprehensive engineering practices for improving Flink's stability, task startup latency, and SQL performance, including high‑availability Kafka connectors, fault‑recovery mechanisms, I/O reductions, asynchronous job upgrades, aggregation optimizations, and future resource‑utilization plans.
This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.
The article explains why massive MySQL tables need periodic cleanup, compares risky drop/truncate approaches, and provides a step‑by‑step guide to safely delete historical data in batches using pt‑archiver with proper parameters, session handling, and post‑deletion maintenance.
This article explains why MySQL subqueries often cause performance bottlenecks, describes the underlying execution mechanism, and presents practical optimization strategies—including semi‑join, materialization, and EXISTS—plus a real‑world case study showing how to rewrite a slow query into a fast join.
This comprehensive guide covers fundamental SQL clauses, aggregate functions, joins, set operators, Oracle-specific objects, essential DML/DDL commands, and a curated collection of 50 interview questions with detailed query examples and performance tips for database professionals.
This article outlines ten practical strategies—including avoiding chat tools for SQL, compressing statements, using SELECT COUNT, adding LIMIT, updating audit fields, logical deletion, table backups, temporary tables, fully qualified names, and careful column changes—to help developers, testers, and DBAs prevent costly database errors in production environments.
This article explains MySQL's lock granularity (table, row, page), the different lock modes such as next‑key, gap and record locks, illustrates common deadlock scenarios with detailed SQL examples, and discusses InnoDB's deadlock prevention strategies and best‑practice solutions.
This guide provides a thorough walkthrough of SQL fundamentals, including SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, aggregate functions, joins, set operations, Oracle-specific objects, DDL/DML commands, and a collection of classic interview questions with complete query examples and performance tips.
This article walks through downloading the required Flink and Hudi components, building Hudi for Scala 2.12, and demonstrates step‑by‑step how to create, populate, query, and update Hudi tables in both batch and streaming modes using Flink SQL, complete with code snippets and result screenshots.
This article compiles 50 practical SQL performance optimization techniques—from indexing strategies and query rewriting to avoiding full table scans, using temporary tables, proper data types, and efficient backup methods—helping developers write faster, more scalable database queries.
This article explains why adding extra conditions after the ON clause in a LEFT JOIN does not reduce the number of rows from the left table, clarifies the difference between ON and WHERE filters, and demonstrates the behavior with practical SQL examples and diagrams.
This article demonstrates how to configure Flink 1.12 to read data from HBase, covering the required environment components, HBase table creation, Maven dependencies, Java POJO and Flink‑SQL code, and showing the query results with and without printing the TableResult.
The article explains that inserting ‘WHERE 1=1’ in dynamically built SQL statements prevents syntax errors when appending additional conditions, illustrates its use with Java string concatenation examples, discusses performance considerations, and shows how to copy tables or duplicate structures using this technique.
This article explains the differences between DROP, DELETE, and TRUNCATE, recommends appropriate MySQL data types for monetary values, compares MyISAM and InnoDB storage engines, outlines transaction ACID properties, describes views, stored procedures versus triggers, indexes, UNION vs UNION ALL, and lists the main SQL language categories.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree leaf page can hold, derives the total record capacity for trees of different heights, and shows how to determine the actual B+‑tree height in a MySQL table using page metadata.
This comprehensive guide explores MySQL’s origins, key characteristics, logical architecture, storage engines, transaction handling, concurrency control, and detailed indexing strategies, offering practical insights and best‑practice recommendations for developers and database administrators seeking to master MySQL performance and reliability.
This article explains the core principles of MySQL indexes, covering their essence, various types such as hash, binary, B‑tree and B+‑tree, the structure of primary key directories and index pages, clustered versus non‑clustered indexes, and the back‑table lookup process.
This guide explains how to identify MySQL max‑connection errors, check the current limit, and increase it using configuration files, global variables, source‑code changes, or mysqld_safe adjustments, with detailed commands and code examples for various MySQL versions.
This comprehensive guide explains what MyBatis is, its advantages and drawbacks, usage scenarios, differences from Hibernate, key syntax like #{ } vs ${ }, mapping techniques, pagination, caching, lazy loading, batch operations, dynamic SQL, and how to configure mappers and plugins in Java projects.
This comprehensive guide explains how to migrate Oracle databases to MySQL by detailing type differences, migration steps, performance tuning, and validation techniques, while highlighting common pitfalls such as data‑type mismatches, character‑set issues, LOB handling, and transaction isolation nuances.
This article explains common reasons why Oracle indexes may be ignored—such as column comparisons, NULL values, NOT conditions, wildcard placement, functions, data type conversions, arithmetic predicates, and special index types—and provides practical SQL examples and tips to ensure indexes are utilized for better query performance.
This article provides a comprehensive, step‑by‑step guide to SQLite, covering table creation, deletion, data manipulation, queries, index management, view creation, trigger usage, and transaction control, complete with code examples and illustrative screenshots for developers of all levels.
This article provides a comprehensive overview of MyBatis, covering its definition as a semi‑ORM persistence framework, core architecture, key components, advantages and drawbacks, practical usage patterns, and a collection of common interview questions with detailed answers for Java developers.
The article explains why both equality and inequality comparisons with a NULL value in MySQL return no rows, illustrating common interview misconceptions and emphasizing the need to grasp NULL semantics for reliable database programming.
This article demystifies MySQL’s query execution by exploring its logical architecture, client‑server protocol, query cache, parsing, optimization, and execution engine, then offers practical indexing and performance‑tuning techniques—including B‑Tree fundamentals, covering indexes, and pagination tricks—to help developers write faster, more efficient SQL.
This article introduces the Laravel‑query‑builder package, explaining how to replace complex IF‑ELSE query logic with a configuration‑driven approach, and provides installation steps, usage examples, and code snippets for building flexible SQL queries in Laravel applications.
This tutorial demonstrates how to use MySQL Shell's three components—MySQL, MySQL X, and SHELL—to connect to a sample WORLD database, perform CRUD operations, execute transactions, and run advanced queries with code examples for each component.
This article explains the multiple reasons why SELECT * slows down MySQL queries—including extra parsing, unnecessary data transfer, increased I/O, and loss of covering index optimization—while also covering index fundamentals, composite indexes, and practical tips for writing efficient SELECT statements.
This guide explains how to use ProxySQL's stats_mysql_query_digest to detect rarely accessed MySQL tables, export table lists, script the retrieval of last access times, and safely rename or drop unused tables after verification, helping reduce storage waste and metadata overhead.
This article provides a thorough overview of relational database fundamentals, covering basic concepts, SQL syntax, DML and DDL commands, functions, joins, unions, grouping, ordering, transactions, permission management, stored procedures, cursors, and triggers, complete with illustrative code examples.
This article explains the purpose and proper use of the SQL HAVING clause, demonstrates how it works with GROUP BY to filter aggregated groups, shows examples for finding groups with specific counts, missing IDs, mode, median, and non‑NULL values, and clarifies best practices for query performance and semantics.
This tutorial explains how to select a technical architecture, design a three‑layer data warehouse (ODS, CDM, ADS), model tables and dimensions, choose storage strategies, handle slowly changing dimensions, synchronize data with DataWorks, and implement dimensional modeling and fact tables using Alibaba MaxCompute for big‑data analytics.
This article explains how JDBC evolves into MyBatis, detailing the seven-step JDBC process, the motivations for encapsulating JDBC, the design of MyBatis components such as SqlSession, Executor, and StatementHandler, and various optimization strategies including connection pooling, SQL management, dynamic parameters, and caching.
