DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Miscellaneous functions

Info Catalog (mysql.info.gz) Information functions (mysql.info.gz) Other Functions
 
 12.8.4 Miscellaneous Functions
 ------------------------------
 
 `DEFAULT(COL_NAME)'
      Returns the default value for a table column.  Starting from MySQL
      5.0.2, you will get an error if the column doesn't have a default
      value.
 
           mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
 
      `DEFAULT()' was added in MySQL 4.1.0.
 
 `FORMAT(X,D)'
      Formats the number X to a format like `'#,###,###.##'', rounded to
      D decimals, and returns the result as a string.  If D is `0', the
      result will have no decimal point or fractional part.
 
           mysql> SELECT FORMAT(12332.123456, 4);
                   -> '12,332.1235'
           mysql> SELECT FORMAT(12332.1,4);
                   -> '12,332.1000'
           mysql> SELECT FORMAT(12332.2,0);
                   -> '12,332'
 
 `GET_LOCK(STR,TIMEOUT)'
      Tries to obtain a lock with a name given by the string STR, with a
      timeout of TIMEOUT seconds.  Returns `1' if the lock was obtained
      successfully, `0' if the attempt timed out (for example, because
      another client has previously locked the name), or `NULL' if an
      error occurred (such as running out of memory or the thread was
      killed with `mysqladmin kill').  If you have a lock obtained with
      `GET_LOCK()', it is released when you execute `RELEASE_LOCK()',
      execute a new `GET_LOCK()', or your connection terminates (either
      normally or abnormally).
 
      This function can be used to implement application locks or to
      simulate record locks.  Names are locked on a server-wide basis.
      If a name has been locked by one client, `GET_LOCK()' blocks any
      request by another client for a lock with the same name. This
      allows clients that agree on a given lock name to use the name to
      perform cooperative advisory locking.
 
           mysql> SELECT GET_LOCK('lock1',10);
                   -> 1
           mysql> SELECT IS_FREE_LOCK('lock2');
                   -> 1
           mysql> SELECT GET_LOCK('lock2',10);
                   -> 1
           mysql> SELECT RELEASE_LOCK('lock2');
                   -> 1
           mysql> SELECT RELEASE_LOCK('lock1');
                   -> NULL
 
      Note that the second `RELEASE_LOCK()' call returns `NULL' because
      the lock `'lock1'' was automatically released by the second
      `GET_LOCK()' call.
 
 `INET_ATON(EXPR)'
      Given the dotted-quad representation of a network address as a
      string, returns an integer that represents the numeric value of
      the address.  Addresses may be 4- or 8-byte addresses.
 
           mysql> SELECT INET_ATON('209.207.224.40');
                   -> 3520061480
 
      The generated number is always in network byte order. For the
      example just shown, the number is calculated as `209*256^3 +
      207*256^2 + 224*256 + 40'.
 
      As of MySQL 4.1.2, `INET_ATON()' also understands short-form IP
      addresses:
 
           mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
                   -> 2130706433, 2130706433
 
      `INET_ATON()' was added in MySQL 3.23.15.
 
 `INET_NTOA(EXPR)'
      Given a numeric network address (4 or 8 byte), returns the
      dotted-quad representation of the address as a string.
 
           mysql> SELECT INET_NTOA(3520061480);
                   -> '209.207.224.40'
 
      `INET_NTOA()' was added in MySQL 3.23.15.
 
 `IS_FREE_LOCK(STR)'
      Checks whether the lock named STR is free to use (that is, not
      locked).  Returns `1' if the lock is free (no one is using the
      lock), `0' if the lock is in use, and `NULL' on errors (such as
      incorrect arguments).
 
      `IS_FREE_LOCK()' was added in MySQL 4.0.2.
 
 `IS_USED_LOCK(STR)'
      Checks whether the lock named STR is in use (that is, locked).  If
      so, it returns the connection identifier of the client that holds
      the lock.  Otherwise, it returns `NULL'.
 
      `IS_USED_LOCK()' was added in MySQL 4.1.0.
 
 `MASTER_POS_WAIT(LOG_NAME,LOG_POS[,TIMEOUT])'
      This function is useful for control of master/slave
      synchronization.  It blocks until the slave has read and applied
      all updates up to the specified position in the master log.  The
      return value is the number of log events it had to wait for to get
      to the specified position.  The function returns `NULL' if the
      slave SQL thread is not started, the slave's master information is
      not initialized, the arguments are incorrect, or an error occurs.
      It returns `-1' if the timeout has been exceeded. If the slave SQL
      thread stops while `MASTER_POS_WAIT()' is waiting, the function
      returns `NULL'.  If the slave is past the specified position, the
      function returns immediately.
 
      If a TIMEOUT value is specified, `MASTER_POS_WAIT()' stops waiting
      when TIMEOUT seconds have elapsed. TIMEOUT must be greater than 0;
      a zero or negative TIMEOUT means no timeout.
 
      `MASTER_POS_WAIT()' was added in MySQL 3.23.32.  The TIMEOUT
      argument was added in 4.0.10.
 
 `RELEASE_LOCK(STR)'
      Releases the lock named by the string STR that was obtained with
      `GET_LOCK()'. Returns `1' if the lock was released, `0' if the
      lock wasn't locked by this thread (in which case the lock is not
      released), and `NULL' if the named lock didn't exist.  The lock
      will not exist if it was never obtained by a call to `GET_LOCK()'
      or if it has previously been released.
 
      The `DO' statement is convenient to use with `RELEASE_LOCK()'.
       `DO' DO.
 
 `UUID()'
      Returns a Universal Unique Identifier (UUID) generated according
      to "DCE 1.1: Remote Procedure Call" (Appendix A) CAE (Common
      Applications Environment) Specifications published by The Open
      Group in October 1997 (Document Number C706).
 
      A UUID is designed as a number that is globally unique in space and
      time. Two calls to `UUID()' are expected to generate two different
      values, even if these calls are performed on two separate
      computers that are not connected to each other.
 
      A UUID is a 128-bit number represented by a string of five
      hexadecimal numbers in `aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
      format:
 
         * The first three numbers are generated from a timestamp.
 
         * The fourth number preserves temporal uniqueness in case the
           timestamp value loses monotonicity (for example, due to
           daylight saving time).
 
         * The fifth number is an IEEE 802 node number that provides
           spatial uniqueness.  A random number is substituted if the
           latter is not available (for example, because the host
           computer has no Ethernet card, or we do not know how to find
           the hardware address of an interface on your operating
           system).  In this case, spatial uniqueness cannot be
           guaranteed. Nevertheless, a collision should have _very_ low
           probability.
 
           Currently, the MAC address of an interface is taken into
           account only on FreeBSD and Linux. On other operating
           systems, MySQL uses a randomly generated 48-bit number.
 
 
           mysql> SELECT UUID();
                   -> '6ccd780c-baba-1026-9564-0040f4311e29'
 
      Note that `UUID()' does not yet work with replication.
 
      `UUID()' was added in MySQL 4.1.2.
 
 
Info Catalog (mysql.info.gz) Information functions (mysql.info.gz) Other Functions
automatically generated byinfo2html