Ian Turton

Recent Blog Comments By Ian Turton

• Using LATERAL Joins To Get "Top N" Records From Each Group In MySQL 8.0.14

  Posted on Dec 2, 2022 at 6:51 PM

  As regards any 'speed' issues... The alternative to doing it in one query (and the way most devs would approach it) would one db call to get the members and a 10x loop to get the 5 top messages. That's 11 times the number of (admittedly simpler) query parsing/optimisations and 11 lots of net... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 11:48 AM

  @Ben, "I like to avoid nullable columns wherever possible". Me too, absolutely. Hate the bloody things. For var/chars I never use them, because I've never come across (others will have I suppose) the need to know whether an empty string is 'really empty' or 'don't know yet'. But ... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 11:03 AM

  @Ben, Ironically (is it, I don't know!) the component I mentioned doesn't bother with deletes because I don't do them. Can't think of the last time I did an 'app based' hard delete on a table (certainly not one anyone would care about me auditing)... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Nov 15, 2022 at 10:59 AM

  @SCOTT, I also do both, and like you only audit a few 'major' tables in any given system. I built a component that can take an insert/update SQL statement, run it, check for triggers and create a table-specific audit record with just the changed columns. I do find 'lastUpdated' occassional... read more »

• A Database Column For "Date Updated" Has No Semantic Meaning, Nor Should It

  Posted on Oct 28, 2022 at 4:48 PM

  @Andrew, Same! I note in passing Ben's code will set updatedAt even if nothing is updated. UPDATE CURRENT_TIMESTAMP doesn't do that. (I'd also make the obvious-in-case-it's-not-to-some point that CURRENT_TIMESTAMP saves you having to remember to update that date anywhere else you updat... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jul 3, 2022 at 8:57 PM

  @Ben, Sweet! 🤓... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 20, 2022 at 12:28 PM

  @Ben, Yes! ...and the reason you're not getting it from the docs (IMO) is they've gone straight to a slightly obfuscatory, non-obvious example. Whereas way back when I found VALUES() I came to it from a 'there must be a way for me to stop repeating all these params cos it's dangerous/ted... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 20, 2022 at 12:07 PM

  @Ben, I reckon the disconnect here is you think I'm trying to 'improve' your final query, the one that gets the id in the event that the upserts ends up being an update. I'm not - it's about the first upsert I'm trying to fix the issue that, using your original upsert example, you have to ... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 18, 2022 at 11:06 AM

  @Ben, you can use the VALUES(col_name) function to refer to column values from the INSERT portion That's it right there. I don't know why they've gone immediately to an example of a non-obvious use for this (e.g. the single insert version of your statement I posted), but I suspect t... read more »

• It's OK To Be Super Explicit In Your Data Access Method Names

  Posted on Jun 18, 2022 at 9:36 AM

  Assuming this is MySQL, I prefer to use the VALUES function on the update part. Apart from being more readable, it removes the possibility that I make a change to the insert part and forget to change the update (or vice versa) so e.g. ON DUPLICATE KEY UPDATE valueOne = VALUES(valueO... read more »