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
      VARCHAR

      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.

      VARCHAR

      The MySQL Varchar Data Type

      The MySQL VARCHAR data type is used to store variable-length character strings, having a length up to 65,535 bytes.
      In MySQL, when you store text in a VARCHAR column, it needs a little extra space to keep track of how long the text is. This extra space can be either 1 or 2 bytes, depending on the length of the text. If the text is short (less than 255 characters), it uses 1 byte for length. For longer text, it uses 2 bytes.
      The total size of data plus the length info cannot exceed 65,535 bytes for a row in a table.

      Example

      In the following query, we are creating a new table named test_table that has two columns column1 and column2.
      As we can see in the below code block, the columns (column1 = 32765 and column2 = 32766) makes 65531 bytes. These columns will take 2 bytes each as a length prefix. Therefore, the columns totally make 32765+2+32766+2 = 65535 bytes −
      CREATE TABLE test_table (
         column1 VARCHAR(32765) NOT NULL,
         column2 VARCHAR(32766) NOT NULL
      )CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;
      

      Output

      Following is the output of the above code −
      Query OK, 0 rows affected (0.03 sec)
      

      Example

      Now, let us create another table test_table2 and provide 32766 and 32766 to both the columns (column1 and column2) −
      CREATE TABLE test_table2 (
         column1 VARCHAR(32766) NOT NULL, --error
         column2 VARCHAR(32766) NOT NULL
      )CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;
      

      Output

      As we can see in the output below, an error is generated because the row size (32766 +2 +32766 +2 = 65536) exceeds the maximum limit (65,535) −
      ERROR 1118 (42000): Row size too large. The maximum row size for the used 
      table type, not counting BLOBs, is 65535. This includes storage overhead, 
      check the manual. You have to change some columns to TEXT or BLOBs
      

      Example

      Here, we are creating another table named CUSTOMERS using the following query −
      CREATE TABLE CUSTOMERS (
         ID int PRIMARY KEY AUTO_INCREMENT,
         NAME VARCHAR(3)
      );
      
      Following is the output obtained −
      Query OK, 0 rows affected (0.03 sec)
      
      Now, we are inserting a string into NAME column where the length is greater than the length of VARCHAR column −
      INSERT INTO CUSTOMERS (NAME) VALUES ('Rahul');
      

      Output

      As a result, MySQL will generate an error given below −
      ERROR 1406 (22001): Data too long for column 'NAME' at row 1
      

      Example

      MySQL does not count the trailing spaces when inserting a value. Instead it truncates the trailing spaces.
      Let us insert a value into the NAME column that has trailing spaces −
      INSERT INTO CUSTOMERS (NAME) VALUES ('ABC ');
      

      Output

      As we can see in the output below, MySQL issued a warning −
      Query OK, 1 row affected, 1 warning (0.02 sec)
      

      Example

      In the following query, we are trying to check the length of the values in NAME column −
      SELECT ID, NAME, length(NAME) FROM CUSTOMERS;
      
      The result produced is as follows −
      ID
      NAME
      length(NAME)
      1
      ABC
      3
      Now, let us execute the below query to display the warnings that issued on the above insertion operation −
      SHOW warnings;
      
      The result produced is −
      Level
      Code
      Message
      Note
      1265
      Data truncated for column 'NAME' at row 1

      Varchar Datatypes Using a Client Program

      In addition to performing datatypes using mysql query, we can also create column of the Varchar datatypes using the client program.

      Syntax

      PHPNodeJSJavaPython
      To create a column of Varchar datatypes through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
      $sql ="CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
      $mysqli->query($sql);
      

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      //create a customer table and use varchar data type with differenet size
      $sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
      if ($mysqli->query($sql)) {
          echo "Table created successfully with varchar data!\n";
      }
      if ($mysqli->errno) {
          printf("table could not create table: %s", $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Table created successfully with varchar data!