128 posts tagged with "SQL"

LIMIT says how many rows to return, and OFFSET says how many to skip, which is all you need to page through results. There's one catch that's easy to miss: without ORDER BY, you don't know which rows you'll get. This guide pages through a real table, and shows that happening.

DELETE removes rows, and outside a transaction there's no undo. Forgetting the WHERE empties the table. This guide shows the habits that keep it safe on MySQL 9.7: check the rows first, confirm how many went, try it in a transaction, and let the database's own guards stop mistakes.

An UPDATE with the wrong WHERE, or no WHERE at all, can change every row in a table, and there's no undo afterwards. A few habits make it safe: check the rows with a SELECT first, read what MySQL reports, and use a transaction when it matters. This guide does all of those on MySQL 9.7.

INSERT adds rows to a table. You list the columns, give their values, and MySQL fills in an AUTO_INCREMENT ID by itself. This guide adds one row, gets its ID back, adds several rows at once, and shows what happens when you leave out a column that's required.

SELECT is how you read from MySQL. You name the columns and the table, then narrow it down with WHERE, sort it with ORDER BY, and cut it short with LIMIT. This guide runs each of those on a real table in MySQL 9.7, and shows what comes back.

An UPDATE in MySQL stopped with ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Safe-updates mode refused it because nothing limited it to particular rows. Usually that's the mode doing what it's for, and the right fix is a better WHERE.

An INSERT into MySQL stopped with ERROR 1364 (HY000): Field 'CreatedAtUtc' doesn't have a default value. The column needs a value, the statement didn't give one, and MySQL has nothing to fall back on. The fix is to send the value, or give the column a default.

I sent a product price of 9.999 to my API, and it answered 201 Created with a price of 10.00. No error, no warning: MySQL rounded the amount to fit its DECIMAL(12,2) column, even in strict mode, and raised only a note that EF Core never shows. For money, silently changing the number isn't acceptable, so the API now refuses it.

A long category name made my API answer 500, with Data too long for column 'Name' at row 1 in the log. MySQL was right to refuse it: the column is varchar(100), and the name had 101 characters. The problem was that the request got as far as MySQL at all. A validation attribute stops it first, and the client gets an answer it can act on.

Right after I added a property to an EF Core entity, every request that touched that table answered 500, and the log said MySqlException: Unknown column 'p.Version' in 'field list'. The code built without a problem, because the C# was fine. The table simply didn't have that column yet: changing the class doesn't change the database until a migration does.