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
      Collation

      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.

      Collation

      MySQL Collation is a set of rules used to decide how to compare and sort various characters of a character set. MySQL supports multiple character sets including ASCII, Unicode System, Binary, etc.
      Every character of these character sets is subjected to a weight. A MySQL collation orders the characters based on their respective weights. For instance, when comparing two characters in a character set, if one character holds heavier weight than the other, it is greater; and vice-versa. If both characters have equal weights, they are equal.
      Each character set must have at least one collation (or more) and no two character sets can have the same collation.

      Implementing MySQL Collations

      MySQL implements various types of collations in order to compare character strings
      • Simple Collations for 8-bit Character Sets
      • Complex Collations for 8-bit Character Sets
      • Collations for Non-Unicode Multibyte Character Sets
      • Collations for Unicode Multibyte Character Sets
      • Miscellaneous Collations
      Every character set has a built-in binary collation, so they need not be redefined. Built-in collations like these must not be modified in any case to avoid unexpected server behaviour.

      Simple Collations for 8-bit Character Sets

      • As the 8-bit character sets can only hold up to 256 characters, this type of collation is implemented by using a weights array of length 256.
      • Each character in the character set is one-to-one mapped to the weights.
      • It is a case-insensitive collation, so the uppercase and lowercase of same character hold the same weight.

      Complex Collations for 8-bit Character Sets

      • For complex 8-bit character sets, collations are implemented by defining the order of characters using functions.
      • Here, we create a C source file that specifies the character set properties and defines the necessary support routines to perform operations on that character set properly.

      Collations for Non-Unicode Multibyte Character Sets

      • Unlike single-byte (8-bit) characters, there are two types of relationships between codes and weights of multi-byte characters.
      • Weight of a character is equal to its code.
      • Character codes are mapped one-to-one with weights, where weights are not necessarily equal to codes.

      Collations for Unicode Multibyte Character Sets

      Some collations are based on the Unicode Collation Algorithm (UCA). They hold the following properties
      • If a character has weight, each weight uses 2 bytes.
      • If a character has no weight, then the character is ignorable.
      • A single character can have many weights. This is called Expansion. For example, the German letter (SHARP S) has a weight of 0x0FEA0FEA.
      • Multiple characters together can have only one weight. This is called Contraction. For example, 'ch' is a single letter in Czech and has a weight of 0x0EE2.

      Miscellaneous Collations

      • Collations that do not fall into any previous categories are termed as Miscellaneous Collations.

      Set Character Set and Collation

      MySQL allows us to set the character sets and collations at three different levels. The same is described below:
      • At Server level
      • At Database level
      • At Table level

      At Server Level

      In MySQL, the character set latin1 will be used as the default character set. So, the default collation will be latin1_swedish_ci. MySQL allows us to change these default settings at the server startup level.
      When starting up a MySQL server, if we specify a character set, it will use the default collation of that set. But if we explicitly specify both a character set and collation, MySQL will use that combination for all databases created further.

      Example

      In the following query, we will set the character set as utf8 and the collation as utf8_unicode_cs for the sever.
      mysqld --character-set-server=utf8 --collation-server=utf8_unicode_cs
      A warning is issued if --collation-server is set to a user-defined collation name.

      At Database Level

      When we create a database and if we do not provide any character set and collation, the database will use the default character set and collation of the server.
      We can override the default character set and collation at the database level using the CREATE DATABASE statement.
      If we want to override default settings for existing database, we can use the ALTER DATABASE statement.

      Syntax

      Following is the basic syntax to override the default settings at database level 
      [CREATE | ALTER] DATABASE database_name  
      CHARACTER SET character_set_name 
      COLLATE collation_name;

      Example

      Here, we are creating a database and specifying the character set as utf8 and collation as utf8_unicode_ci using the following query 
      CREATE DATABASE testdb
      CHARACTER SET utf8
      COLLATE utf8_unicode_ci;

      At Table Level

      In MySQL, a database may contain tables with different characters sets and collations than the database's character set and collation.
      We can specify the default character set and collation at the while creating the table using the CREATE TABLE statement.
      If we want to override default settings for existing table, we can use the ALTER TABLE statement.

      Syntax

      Following is the syntax for specifying default character set and collation for a table using the CREATE TABLE statement 
      [CREATE | ALTER] TABLE table_name
      column_name datatype (length)
      CHARACTER SET character_set_name
      COLLATE collation_name

      Example

      In the following query, we are creating a table without any character set and collation. So, it uses the database's character set and collation.
      CREATE TABLE CUSTOMERS(
         ID VARCHAR(45),
         NAME VARCHAR(45),
         AGE INT
      );
      Now, we are using the ALTER TABLE statement to modify the character set as 'latin1' and collation as 'latin_german_ci'.
      ALTER TABLE CUSTOMERS
      CHARACTER SET latin1
      COLLATE latin1_german1_ci;

      Displaying Default Collations

      We can display all the default collations of character sets in MySQL database server using the SHOW CHARACTER SET query.
      SHOW CHARACTER SET;
      User-defined collations are deprecated in the latest versions of MySQL. Thus, the server issues a warning if they are used in any SQL statement.
      A collation string for every character set starts with the character set name and ends with _ci (case insensitive), _cs(case sensitive) or _bin(binary).

      The MySQL LIKE Clause

      In MySQL, using the LIKE clause with the SHOW COLLATION statement, we can specify a pattern to fetch the names and other information of the collations that match the given pattern.
      SHOW COLLATION LIKE 'greek%';

      Output

      The above query returns all the collations with the name greek in it.
      Collation
      Charset
      Id
      Default
      Compiled
      Sortlen
      greek_bin
      greek
      70
      
      Yes
      1
      greek_general_ci
      greek
      25
      Yes
      Yes
      1

      The MySQL WHERE Clause

      We can use the WHERE clause with the SHOW COLLATION statement to retrieve collation names that match the specified condition.
      SHOW COLLATION WHERE Charset = 'cp1251';

      Output

      The above query returns all the collations where the charset id equal to 'cp1251'.
      Collation
      Charset
      Id
      Default
      Compiled
      Sortlen
      cp1251_bin
      cp1251
      50
      
      Yes
      1
      cp1251_bulgarian_ci
      cp1251
      14
      
      Yes
      1
      cp1251_general_ci
      cp1251
      51
      Yes
      Yes
      1
      cp1251_general_cs
      cp1251
      52
      
      Yes
      1
      cp1251_ukrainian_ci
      cp1251
      23
      
      Yes
      1