Mysql> SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.With more than 25 photos & 90 likes every second, we store a lot of data here at Instagram. Type '\c' to clear the current input statement. Other names may be trademarks of their respective Oracle is a registered trademark of Oracle Corporation and/or itsĪffiliates. Version 8.0.16 test: Server version: 8.0.16 MySQL Community Server - GPLĬopyright (c) 2000, 2019, Oracle and/or its affiliates. Regardless, the SQL statements above illustrate the defect, and and I have shown that this bug has been fixed in version 5.7.27 (exactly) and version 8.0.16 (possibly all 8., only tested 8.0.16 and 8.0.18). I did a quick search of their bug DB but I did not find a mention of it. I wouldn't recommend doing this in code it smells. In my case I am working within a sql migration file where I can string together this kind of sequence of sql in a cohesive manner. This is not very elegant, but it does work. SELECT UNHEX(REPLACE(generatedId,'-','')), tableName FROM GeneratedIds INSERT INTO MyTable (uid, tableName) - uid is binary(16) SELECT UUID(), TABLE_NAME from INFORMATION_SCHEMA.TABLES INSERT INTO GeneratedIds (generatedId, tableName) Until I can get others to agree to a version change, I'm moving forward by using a temporary table as intermediate storage for generated id values. I'd love to change the PK column definitions to default the values as has suggested. I'll discuss upgrading the deployed engine version. Looking in the AWS console, I see support up to version 5.7.26 (in the 5.7 vein) and 8.0.16 (in the 8.0 vein). The Terraform (scripted deploy) spins up an AWS RDS instance with engine version 5.7.16. Mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64)īut this will end up deploying to an AWS RDS instance. Is there a way to perform an "Insert From Select" and generate a unique UUID in binary 16 form per row in the select? I also tested this behavior with another non-deterministic function, RAND(), and in this case the REPLACE() function worked as we'd expect! SELECT REPLACE(RAND(),' ',' ') from INFORMATION_SCHEMA.TABLES LIMIT 3 I would have expected the non-deterministic characteristic of the UUID() function to result in the REPLACE() function behaving as if it had a different argument for each row, but it seems as though the DB engine is over optimizing by assuming the UUID() to be constant. The UUID() function is non-deterministic, while the REPLACE() function is deterministic. I can't change that please don't focus on that. It shouldn't matter, but for those that are curious, our PK's are UUIDs in binary(16) form. Note, I am using the information schema's list of tables for convenience. SELECT UNHEX(REPLACE(UUID(),'-','')), TABLE_NAME from INFORMATION_SCHEMA.TABLES This 'breaks' Insert From Select statements like this where we expect each inserted row to have a unique value: INSERT INTO MyTable (uid, tableName) - uid is binary(16) However, as soon as we use it within the REPLACE() function, it begins producing the same value: SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.TABLES LIMIT 3 The UUID() function by itself produces a different value each time it is called, as I would expect it to do: SELECT UUID() from INFORMATION_SCHEMA.TABLES LIMIT 3
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |