Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      UUID

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      UUID

      The MySQL UUID function

      The MySQL UUID() function is used to generate "Universal Unique Identifiers" (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number.

      UUID Format

      The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens.
      The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value.

      Generating a UUID

      Following is the basic example to generate a UUID using the UUID() function in MySQL 
      SELECT UUID();

      Output

      It will display a universal unique identifier as shown below
      UUID()
      55f7685d-e99c-11ed-adfc-88a4c2bbd1f9

      Generating Multiple UUIDs

      You can generate multiple UUIDs in a single query, and each UUID will be different as shown below 
      SELECT UUID() AS ID1, UUID() AS ID2;

      Output

      The output will show two different UUIDs, with differences generally in the first segment
      ID1
      ID2
      78c3fb43-e99c-11ed-adfc-88a4c2bbd1f9
      78c3fb4f-e99c-11ed-adfc-88a4c2bbd1f9

      UUIDs in a Database Table

      You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data
      Here, we are first creating a table with the name "ORDERS", with an ORDER_ID column of type VARCHAR using the following query 
      CREATE TABLE ORDERS(
         ID int auto_increment primary key,
         NAME varchar(40),
         PRODUCT varchar(40),
         ORDER_ID varchar(100)
      );
      Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column 
      INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Varun", "Headphones", UUID());
      INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Priya", "Mouse", UUID());
      INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Nikhil", "Monitor", UUID());
      INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Sarah", "Keyboard", UUID());
      INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Vaidhya", "Printer", UUID());
      Following is the ORDERS table obtained
      ID
      NAME
      PRODUCT
      ORDER_ID
      1
      Varun
      Headphones
      a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
      2
      Priya
      Mouse
      a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9
      3
      Nikhil
      Monitor
      a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
      4
      Sarah
      Keyboard
      a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
      5
      Vaidhya
      Printer
      a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

      Modifying UUIDs

      You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64().

      Example

      Here, we are updating the UUID value for the record where ID = 1 using the following query 
      UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1;

      Output

      Following is the output of the above code 
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 0

      Verification

      To verify the modified UUID values, we can use the following SELECT query 
      SELECT * FROM ORDERS;
      As we can see in the output below, every time we execute the UUID() function, we get a different UUID value
      ID
      NAME
      PRODUCT
      ORDER_ID
      1
      Varun
      Headphones
      38f4d94a-e99d-11ed-adfc-88a4c2bbd1f9
      2
      Priya
      Mouse
      a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9
      3
      Nikhil
      Monitor
      a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
      4
      Sarah
      Keyboard
      a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
      5
      Vaidhya
      Printer
      a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

      Example

      Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below
      
      UPDATE ORDERS 
      SET ORDER_ID = REPLACE(UUID(), '-', '')
      WHERE ID = 2;

      Output

      Output of the above code is as follows 
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 0

      Verification

      To verify the modified UUID value, we can use the following SELECT query 
      SELECT * FROM ORDERS;
      As we can see in the output below, the UUID of row = 2 is modified without disturbing the "unique" part of it
      ID
      NAME
      PRODUCT
      ORDER_ID
      1
      Varun
      Headphones
      a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
      2
      Priya
      Mouse
      069b0ca-7e99e11ed-adfc-88a4c2bbd1f9
      3
      Nikhil
      Monitor
      a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
      4
      Sarah
      Keyboard
      a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
      5
      Vaidhya
      Printer
      a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

      Example

      In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function 
      UPDATE ORDERS 
      SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),'-',''))) 
      WHERE ID=4;

      Output

      The result produced is as follows 
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 0

      Verification

      Let us verify the modified UUID value using the following SELECT query 
      SELECT * FROM ORDERS;
      The output produced is as given below
      ID
      NAME
      PRODUCT
      ORDER_ID
      1
      Varun
      Headphones
      a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
      2
      Priya
      Mouse
      069b0ca7-e99e11ed-adfc-88a4c2bbd1f9
      3
      Nikhil
      Monitor
      a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
      4
      Sarah
      Keyboard
      ObRYA+mfEe2t/IikwrvR+Q==
      5
      Vaidhya
      Printer
      a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9