SQL that runs, indexes that earn their keep, and migrations that don't take the site down.
WordPress stores a one-way password hash in wp_users.user_pass, a VARCHAR(255) column. Historically a phpass $P$ portable hash (MD5-based but stretched), and bcrypt for new hashes since WordPress 6.8 in 2025.
When to embed related data as subdocuments and when to reference it by _id in MongoDB. The 16 MB document limit, $lookup joins, access patterns, and the established schema-design patterns.
The column type for a password in MySQL is VARCHAR(255). You store the encoded output of a slow password hash (bcrypt, argon2id, scrypt), never a raw MD5 or SHA-256, and you never query the table by password.
Store an Argon2id password hash in MySQL or MariaDB the right way: VARCHAR(255), never a fixed-width column. The encoded format, why its length varies, computing it in PHP / Python / Node, OWASP parameters, and a worked users schema.
![How to store an array in PostgreSQL: native array column types declared with [], the curly-brace and ARRAY[...] literal forms, containment and overlap queries with @> and &&, ANY() membership, and a GIN index.](https://images.techearl.com/store-array-postgresql/store-array-postgresql-1536.avif?v=2026-05-30T23%3A36%3A29Z "Store an Array in PostgreSQL: text[], @>, and GIN Indexing (2026)")
PostgreSQL has native array types: any base type can be an array, declared with []. How to insert with the curly-brace literal or ARRAY[...], query with @>, &&, and ANY(), index with GIN, and when an array beats a junction table or jsonb.
A bcrypt hash is always 60 characters. The right MySQL column is VARCHAR(255) (the future-proof default) or CHAR(60) (the exact fit). Why, plus the app-side hashing and a worked schema for MySQL and MariaDB.
A bcrypt hash is a fixed 60-character string. In PostgreSQL the right column is text (varchar(255) is equivalent). Why text over char(60), app-side hashing, the pgcrypto crypt() option, and a worked users schema.
MySQL has no real boolean type. BOOL and BOOLEAN are just aliases for TINYINT(1). Here is why TINYINT(1) is the idiomatic choice, how BIT(1) trips up ORMs, and a worked is_active schema.
DATETIME vs TIMESTAMP in MySQL: one is time-zone naive, the other converts to UTC and back. How to store instants safely, when to keep a separate IANA zone name, and why MySQL has no timestamptz.
The right column for an email in MySQL is VARCHAR(255), utf8mb4, with a UNIQUE index. Why 255, why not TEXT, how to enforce case-insensitive uniqueness, and a worked users schema.
MySQL ENUM is compact and fast, but adding a value is a schema change and there is nowhere to hang metadata. When to use ENUM, when to use a lookup table with a foreign key, and where VARCHAR + CHECK fits.
The best way to store an IP address in MySQL: INT UNSIGNED for IPv4-only, VARBINARY(16) for IPv6 (or both). Why VARCHAR(45) is the wrong default, the INET conversion functions, and a worked schema.
PostgreSQL has native network types: inet for a host address, cidr for a subnet. Both validate on insert, hold IPv4 and IPv6, and support real containment operators. Why text is the wrong default, plus a worked schema.
PostgreSQL has two JSON types. json keeps an exact text copy and reparses on every read; jsonb stores a decomposed binary format you can index with GIN. When to use each, the operators, and a worked products schema with a containment index.
MySQL has a native JSON column type that validates on insert, stores a parsed binary format, and gives you path operators like ->>. When to reach for it over TEXT, how to index it with a generated column, and the MariaDB difference.
TEXT vs BLOB in MySQL: TEXT is for character data and has a charset, BLOB is for raw bytes and does not. The size families, the row-limit and indexing gotchas, and why files belong in object storage, not a BLOB.
How to store an MD5 hash in PostgreSQL: the raw 16-byte bytea you should usually reach for, the readable text/char(32) hex string, and the uuid trick. Storage cost, encode()/decode() instead of HEX/UNHEX, and the pgcrypto digest() function.
Never use FLOAT or DOUBLE for money. Use DECIMAL(19,4) for exact fixed-point amounts, or BIGINT integer cents for high-volume ledgers, and always store the ISO 4217 currency code separately. Comparison, worked schema, and the rounding rules.
Never store money as a BSON double. Use the Decimal128 type for exact decimal amounts, or store integer cents in a 64-bit NumberLong for high-volume ledgers, and always keep the ISO 4217 currency code in its own field. Comparison, worked documents, and the NumberDecimal string gotcha.
Store money in PostgreSQL as numeric (decimal), not the locale-dependent money type and never a float. numeric(19,4) for exact amounts, bigint cents for high-volume ledgers, plus a separate char(3) ISO 4217 currency. Comparison and worked schema.
Store a phone number in MySQL as a string, never an integer. Normalize to E.164 and use VARCHAR(16), index it for lookups, and keep the raw input in a second column. Worked schema for MySQL and MariaDB.
How to store a SHA-256 hash in MySQL or MariaDB: CHAR(64), VARCHAR(64), or the BINARY(32) you should usually reach for. Storage cost, index size, the SHA2() function, and indexed generated columns.
How to store a UUID in MongoDB the right way: as a 16-byte BSON Binary of subtype 4, not a 36-character string. Covers mongosh UUID(), driver representations, the legacy subtype 3 byte-order mess, and ObjectId vs UUID for _id.
How to store a UUID in MySQL or MariaDB: the readable CHAR(36) string or the BINARY(16) you should usually reach for. Storage cost, index size, UUID_TO_BIN/BIN_TO_UUID, the swap_flag trick, and why random UUID primary keys wreck InnoDB inserts.
How to store a UUID in PostgreSQL: use the native 16-byte uuid type, not text or varchar(36). Covers gen_random_uuid(), uuid-ossp, why random v4 primary keys hurt index locality, and UUIDv7.
The alias-swap pattern I use in production to change Elasticsearch mappings without taking the search down. Walks through the five steps, gives you a parameterized bash script that runs the reindex and rolls back if needed, and verifies the technique against Elasticsearch 8.x and 9.x.
MyISAM has no transactions, no foreign keys, and corrupts on crash. InnoDB has been the MySQL default since 5.5 and is the only engine that gets new features. Here is how to convert every MyISAM table in a MySQL database with a single SQL script.
MySQL 5.7 has been past its EOL since October 2023. Here is the migration to 8.0: prerequisites, dry-run with mysqlcheck, in-place upgrade, the authentication-plugin change that breaks old clients, and rollback if it goes sideways.
MySQL 8.4 is the new LTS branch, with Premier Support through April 2029 and Extended Support through April 2032. The 8.0 to 8.4 upgrade is much smaller than 5.7 to 8.0, but removed options and the new authentication_policy variable still bite. Full procedure with rollback.
Add a column to a MySQL table with ALTER TABLE ADD COLUMN. Covers DEFAULT values, NOT NULL on existing rows, AFTER positioning, and ALGORITHM=INSTANT on MySQL 8.0.12+.
Change a MySQL column type with ALTER TABLE MODIFY or CHANGE. Covers data preservation, CAST behavior, INT to BIGINT, VARCHAR widening, and how ALGORITHM applies.
Delete duplicate rows in MySQL while keeping one per group, using DELETE JOIN, ROW_NUMBER with CTE, or the safe temp-table swap. With dry-run, transactions, and rollback.
Export a MySQL table to CSV using SELECT INTO OUTFILE, mysqldump --tab, the mysql client with a piped redirect, or a no-privilege one-liner that works on hosts where FILE is disabled.
Find duplicate rows in MySQL with GROUP BY HAVING, a ROW_NUMBER window function, or a self-join. Includes NULL behaviour, soft duplicates, and the right index.
Format a MySQL DATETIME in a SELECT using DATE_FORMAT, common patterns for ISO-8601 and human-readable output, and CONVERT_TZ for timezone-correct dashboards.
Import a CSV into MySQL using LOAD DATA INFILE, LOAD DATA LOCAL INFILE, or the mysqlimport command. Covers header rows, encoding, the FILE privilege, and broken CSVs.
JOIN two MySQL tables with INNER, LEFT, and RIGHT JOIN. With a real example, when each one matches, and the duplicate-row multiplication mistake everyone makes once.
MySQL 8.0 made many ALTER TABLE operations instant. For the heavy ones that still rebuild the table — column type changes, charset conversion, primary-key changes — native online DDL, gh-ost, and pt-online-schema-change each fit a different situation. Compared on a real production schema.
Reset a forgotten MySQL root password using --skip-grant-tables, an init-file, or my.cnf. Step-by-step for MySQL 5.7, 8.0, and the changes that landed in 8.4.
Find rows in one MySQL table that do not exist in another using LEFT JOIN IS NULL, NOT EXISTS, or NOT IN. Each method's NULL behaviour and performance trade-off.
MariaDB started as a MySQL fork and is binary-compatible up to a point. Where the wire protocol stays the same, where the SQL dialect diverges, and how to migrate a real database without losing anything.
MySQL utf8 is a 3-byte alias for utf8mb3 that cannot store emoji or many CJK characters. utf8mb4 is the real UTF-8 and has been the default since MySQL 8.0. The migration looks like ALTER TABLE but bites on index length and ROW_FORMAT.
Practitioner reference for Elasticsearch 9.x: index and document operations, Query DSL, aggregations, vector / kNN search, ESQL, cluster management, version compatibility notes, and the gotchas that bite first-time operators.
MySQL cheat sheet covering CLI commands, database and table operations, joins, indexes, backups, user management, and transactions, with version notes for 5.7, 8.0, and 8.4.
Back up every MySQL (and MariaDB) database in one command with mysqldump, including --single-transaction for InnoDB consistency, gzip compression, restore steps, and the modern alternatives (MySQL Shell, Percona XtraBackup).
Four ways to store an MD5 hash in MySQL or MariaDB: CHAR(32), VARCHAR(32), BINARY(16), and the two-BIGINT split. Storage cost, index speed, and when to use each (plus when to use a stronger hash instead).
Every MySQL data type with exact storage size, value range, and when to use it. Numeric, string, date/time, JSON, spatial, binary, and the new VECTOR type added in MySQL 9.0.