MySQL 实验室 因为专注,所以专业。

  • 首页
  • 博客
  • 下载
  • 文档
  • 工具
  • 知识库
  • 培训及服务
  • MySQL 5.1 Reference Manual :: 11 Functions and Operators :: 11.11 Other Functions :: 11.11.1 Bit Functions
    • MySQL 5.1 Reference Manual
    • Preface, Notes, Licenses
    • 1 General Information
    • 2 Installing and Upgrading MySQL
    • 3 Tutorial
    • 4 MySQL Programs
    • 5 MySQL Server Administration
    • 6 Backup and Recovery
    • 7 Optimization
    • 8 Language Structure
    • 9 Internationalization and Localization
    • 10 Data Types
    • 11 Functions and Operators
    • 12 SQL Statement Syntax
    • 13 Storage Engines
    • 14 High Availability and Scalability
    • 15 MySQL Enterprise Monitor
    • 16 Replication
    • 17 MySQL Cluster NDB 6.X/7.X
    • 18 Partitioning
    • 19 Stored Programs and Views
    • 20 INFORMATION_SCHEMA Tables
    • 21 Connectors and APIs
    • 22 Extending MySQL
    • A MySQL 5.1 Frequently Asked Questions
    • B Errors, Error Codes, and Common Problems
    • C MySQL Change History
    • D Restrictions and Limits
    • Index
    • Standard Index
    • C Function Index
    • Command Index
    • Function Index
    • INFORMATION_SCHEMA Index
    • Transaction Isolation Level Index
    • JOIN Types Index
    • Operator Index
    • Option Index
    • Privileges Index
    • SQL Modes Index
    • Status Variable Index
    • Statement/Syntax Index
    • System Variable Index

    11.11.1. Bit Functions

    Table 11.16. Bitwise Functions

    Name Description
    BIT_COUNT() Return the number of bits that are set
    & Bitwise AND
    ~ Invert bits
    | Bitwise OR
    ^ Bitwise XOR
    << Left shift
    >> Right shift

    MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

    • |

      Bitwise OR:

      mysql> SELECT 29 | 15;
              -> 31
      

      The result is an unsigned 64-bit integer.

    • &

      Bitwise AND:

      mysql> SELECT 29 & 15;
              -> 13
      

      The result is an unsigned 64-bit integer.

    • ^

      Bitwise XOR:

      mysql> SELECT 1 ^ 1;
              -> 0
      mysql> SELECT 1 ^ 0;
              -> 1
      mysql> SELECT 11 ^ 3;
              -> 8
      

      The result is an unsigned 64-bit integer.

    • <<

      Shifts a longlong (BIGINT) number to the left.

      mysql> SELECT 1 << 2;
              -> 4
      

      The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.

    • >>

      Shifts a longlong (BIGINT) number to the right.

      mysql> SELECT 4 >> 2;
              -> 1
      

      The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.

    • ~

      Invert all bits.

      mysql> SELECT 5 & ~1;
              -> 4
      

      The result is an unsigned 64-bit integer.

    • BIT_COUNT(N)

      Returns the number of bits that are set in the argument N.

      mysql> SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
              -> 4, 3