Strings function in MySQL

[Dino M]

I have recently started learning the MySQL. I have just completed some basic things, so I am not having much knowledge in this programming language. So you can explain me considering as newbie. I want to know how to use string function in MySql? Also I want some help regarding about the date and time function along with the string. I am sure that some members hanging out there will help me soon.

[Kallol]

The columns of type string, since MySQL 4.1, can have a CHARACTER SET attribute that indicates the character set used for the column, and an attribute that indicates the COLLATE collation on. Here's an example:

CREATE TABLE table
 (
     c1 CHAR (20) CHARACTER SET utf8
     c2 CHAR (20) CHARACTER SET latintrial COLLATE latin1_bin
 );

In this table we have defined the column c1 with utf8 character set and its default collation, and the column c2 with latin1 character set and its binary collation. The specified length is relative to the number of characters (the number of bytes it can vary in character sets used and the content of the column). We see the types of fields provided:

[NATIONAL] CHAR (M) [BINARY | ASCII | UNICODE] 
[NATIONAL] VARCHAR (M) [BINARY] 
BINARY (M) 
VARBINARY (M) 
TINYBLOB 
TINYTEXT 
BLOB [(M)] 
TEXT [(M)] 
MEDIUMBLOB 
MEDIUMTEXT 
LONGBLOB 
LONGTEXT 
ENUM ('value1', 'value2',...) 
SET ('value1', 'value2',...)

[Appaji]

• CHAR is a fixed-length string (M) filled with spaces right at the time of storage, which are discarded in the process of reading. The expected length is from 0 to 255 characters. NATIONAL option indicates that the string must use the default character set. The BINARY attribute means that should be used in the binary collation of the character set used. Assigns the ASCII character set latin1, unicode ucs2 assigns.
  
• CHAR BYTE is equivalent to CHAR BINARY. Note that if a line has a variable length (ie if at least one variable length column is defined) any CHAR field longer than 3 characters will be converted to VARCHAR.
  
• VARCHAR is a variable-length string, its characteristics have changed as of MySQL 5.0.3: Previously it was 255 and the maximum length of the blanks on the right were eliminated during storage, but now this is no longer and declared maximum length is up to 65535 characters. NATIONAL BINARY attributes and have the same meaning as in CHAR. If you define a VARCHAR column with less than 4 characters will be converted into a CHAR.
  
• BINARY and VARBINARY are in CHAR and VARCHAR, but store strings of bytes instead of characters. They have then character set. BINARY values to the right of receiving a fill byte 0x00 from MySQL 5.0.15 and prior to filling the space was removed and was being read. VARBINARY values, up to MySQL 5.0.3, trailing spaces were removed in reading.

[Mahendra varma]

The BLOB and TEXT formats are used respectively for binary and text. The maximum length is 255 characters and TINYBLOB TINYTEXT, 65535 for BLOB and TEXT, and 16,777,215 for MEDIUMBLOB MEDIUMTEXT, 4 gigabytes for LONGBLOB and LONGTEXT. For the latter, but we must keep in mind the limitations of packet size in client / server protocol and those of memory. You can also declare a BLOB or TEXT column of length, in bytes, in which case the server will choose the smallest type that can contain the required characters (eg. With BLOB (100000) will create a MEDIUMBLOB). If you try to insert a value too long in the fields, with strict mode you will have an error, without strict mode the value will be truncated to the right and there will be a warning if the truncated characters are not spaces. An ENUM column can have one of the values listed in the definition, or NULL or an empty string, which is assigned when you try to insert an invalid value. The possible values can be up to 65535. A SET column, such as ENUM provides a set of possible values (up to 64), but in this case the column can also take more than one value, or none.

[GunFighter]

The columns for dates and time are as follows:

DATE
DATETIME
TIMESTAMP [(M)]
TIME
YEAR [(2 | 4)]

A DATE column may contain dates of '1000-01-01 '(1 January 1000) to '9999-12-31' (31 December 9999). MySQL displays dates in the format that we have just shown, but allows you to insert them as strings or numbers. A DATETIME column contains a date and time, with the same range as for DATE. The display is in format 'YYYY-MM-DD HH: MM: SS', but in this case can be used different formats for insertion.

[Shanbaag]

Prior to MySQL 5.0.2 was always possible to insert date or datetime to 0, or zero value to the day (or day and month) of a date. It was also possible to indicate invalid dates (eg. '1999-04-31 '). Starting from MySQL 5.0.2 these behaviors are controlled by some values of SQL mode (v.lez.4):

• ALLOW_INVALID_DATES is necessary to allow invalid dates: in his absence, invalid dates will cause an error in strict mode, without strict mode will be converted to 0 with a warning;
• NO_ZERO_DATE not accept dates to 0 ('0000-00-00 '): in strict mode will cause an error unless it is used IGNORE; without strict mode will still be accepted with a warning;
• NO_ZERO_IN_DATE accepts values 0 to day and month: it will generate error in strict mode, or enter a date 0 with IGNORE, without strict mode will be accepted with a warning

Can be stored in a TIMESTAMP values analogous to the Unix timestamp, ranging from midnight on 1 January 1970 to an indeterminate time of the year 2037. This type of data is useful for when to involuntarily store a table row: for MySQL can automatically set a TIMESTAMP column in a table when you make an INSERT or UPDATE. The timestamp is displayed in the same format as DATETIME, you can get it in digital format by counting +0 to the column in the SELECT.

[X-Ray]

Until MySQL 4.0 features the timestamp were different from today. Was first displayed in numeric format, and there was a possibility of determining the number of digits displayed indicating the value of M. From MySQL 4.1 onward, this is not possible. In addition, the automatic update feature was available only for the first column defined as TIMESTAMP in each table. Now you can have this operation, even for a column next to the first. We see the possible definition of a TIMESTAMP column:

 tmst TIMESTAMP DEFAULT CURRENT_TIMESTAMP
         ON UPDATE CURRENT_TIMESTAMP

With this statement, the column is automatically initialized and updated to any changes in line with the timestamp of the moment. If you omit one of two statements, only the other will be active and obviously for the DEFAULT clause can also provide a constant value. If you omit both statements on the first column, this will still be automatically initialized and updated. If you want to use automatic value on a column next to the first, you should disable this behavior for the first column using an explicit value of default (eg. DEFAULT 0), and indicate the clauses given above for the column you need. In this case, the omission of both shall not be automatic initialization and updating.