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
      Character Set

      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.

      Character Set

      The MySQL Character Set

      The MySQL Character set is used to determine the set of permissible characters within a string. It allows for the storage of data in various character encodings. By default, MySQL uses the "utf8mb4" character set.
      These character sets provides several functionalities −
      • Storage Variety − MySQL allows the storage of strings in various character sets.
      • String Comparison − Collations help in comparing strings based on the chosen character set.
      • Mixed Character Sets − It is possible to combine strings with different character sets or collations within the same server, database, or table.
      • Specifying Character Set and Collation − You can define the character set and collation at different levels of the database structure.

      The MySQL Show Character-Set

      You can use the MySQL SHOW CHARACTER SET statement to view the list of all the available character sets.

      Syntax

      Following is the syntax of the SHOW CHARACTER SET statement 
      SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]

      Example

      The following query retrieves a list of available character sets, along with their descriptions, default collations, and maximum lengths in a MySQL database 
      SHOW CHARACTER SET;

      Output

      Following is the output obtained
      Charset
      Description
      Default collation
      Maxlen
      armscii8
      ARMSCII-8 Armenian
      armscii8_general_ci
      1
      ascii
      US ASCII
      ascii_general_ci
      1
      big5
      Big5 Traditional Chinese
      big5_chinese_ci
      2
      binary
      Binary pseudo charset
      binary
      1
      cp1250
      Windows Central European
      cp1250_general_ci
      1
      cp1251
      Windows Cyrillic
      cp1251_general_ci
      1
      cp1256
      Windows Arabic
      cp1256_general_ci
      1
      cp1257
      Windows Baltic
      cp1257_general_ci
      1
      cp850
      DOS West European
      cp850_general_ci
      1
      cp852
      DOS Central European
      cp852_general_ci
      1
      cp866
      DOS Russian
      cp866_general_ci
      1
      cp932
      SJIS for Windows Japanese
      cp932_japanese_ci
      2
      dec8
      DEC West European
      dec8_swedish_ci
      1
      eucjpms
      UJIS for Windows Japanese
      eucjpms_japanese_ci
      3
      euckr
      EUC-KR Korean
      euckr_korean_ci
      2
      gb18030
      China National Standard GB18030
      gb18030_chinese_ci
      4
      gb2312
      GB2312 Simplified Chinese
      gb2312_chinese_ci
      2
      gbk
      GBK Simplified Chinese
      gbk_chinese_ci
      2
      geostd8
      GEOSTD8 Georgian
      geostd8_general_ci
      1
      greek
      ISO 8859-7 Greek
      greek_general_ci
      1
      hebrew
      ISO 8859-8 Hebrew
      hebrew_general_ci
      1
      hp8
      HP West European
      hp8_english_ci
      1
      keybcs2
      DOS Kamenicky Czech-Slovak
      keybcs2_general_ci
      1
      koi8r
      KOI8-R Relcom Russian
      koi8r_general_ci
      1
      koi8u
      KOI8-U Ukrainian
      koi8u_general_ci
      1
      latin1
      cp1252 West European
      latin1_swedish_ci
      1
      latin2
      ISO 8859-2 Central European
      latin2_general_ci
      1
      latin5
      ISO 8859-9 Turkish
      latin5_turkish_ci
      1
      latin7
      ISO 8859-13 Baltic
      latin7_general_ci
      1
      macce
      Mac Central European
      macce_general_ci
      1
      macroman
      Mac West European
      macroman_general_ci
      1
      sjis
      Shift-JIS Japanese
      sjis_japanese_ci
      2
      swe7
      7bit Swedish
      swe7_swedish_ci
      1
      tis620
      TIS620 Thai
      tis620_thai_ci
      1
      ucs2
      UCS-2 Unicode
      ucs2_general_ci
      2
      ujis
      EUC-JP Japanese
      ujis_japanese_ci
      3
      utf16
      UTF-16 Unicode
      utf16_general_ci
      4
      utf16le
      UTF-16LE Unicode
      utf16le_general_ci
      4
      utf32
      UTF-32 Unicode
      utf32_general_ci
      4
      utf8mb3
      UTF-8 Unicode
      utf8mb3_general_ci
      3
      utf8mb4
      UTF-8 Unicode
      utf8mb4_0900_ai_ci
      4

      The MySQL Set Character-set

      The MySQL SET CHARACTER SET Statement is used to assign a value to the character set attribute. It maps all the strings between the server and the current client with the specified mapping set. This statement changes values of the "character_set_client" and "character_set_results" variables.

      Syntax

      Following is the syntax of the MySQL SET CHARACTER SET Statement 
      SET {CHARACTER SET | CHARSET} {'charset_name' | DEFAULT}
      Where, 'charset_name' is the name of the character set.

      Example

      The query given below sets the character set to "macroman" 
      SET CHARACTER SET macroman;

      Output

      The output produced is as shown below 
      Query OK, 0 rows affected (0.10 sec)

      Verification

      You can verify the character set values using the SHOW VARIABLES LIKE statement as shown below 
      SHOW VARIABLES LIKE "character_set_client";
      Following is the output obtained
      Variable_name
      Value
      character_set_client
      macroman
      Now verifying the current value of the "character_set_results" variable 
      SHOW VARIABLES LIKE "character_set_results";
      The result produced is as shown below
      Variable_name
      Value
      character_set_results
      macroman