MySQL Administrator’s Bible- P17

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
55
lượt xem
6
download

MySQL Administrator’s Bible- P17

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator’s Bible- P17: Welcome to the MySQL Administrator’s Bible. Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical application information, as well as reference and background information that provide a context for what you are learning.

Chủ đề:
Lưu

Nội dung Text: MySQL Administrator’s Bible- P17

  1. MySQL Proxy A TABLE A-5 (continued ) Constant Name Context Meaning BACKEND_TYPE_RW proxy.backends The backend server was defined [x].type with --proxy-backend -address. MYSQLD_PACKET_ERR auth.packet:byte() This packet contains an error. proxy.response.type MYSQLD_PACKET_OK auth.packet:byte() This packet was successful. proxy.response.type MYSQLD_PACKET_RAW proxy.response.type This packet contains raw data. PROXY_IGNORE_RESULT Return Do not send the results to the client. PROXY_SEND_QUERY Return Send the query queue to the server. PROXY_SEND_RESULT Return Send the results to the client. PROXY_VERSION N/A Returns the proxy version, in hex. 0x00700 corresponds to version 0.7.0 COM_SLEEP string.byte(packet) This packet contains a sleep command. COM_QUIT string.byte(packet) This packet contains a quit command. COM_INIT_DB string.byte(packet) This packet contains a init db command. COM_QUERY string.byte(packet) This packet contains a query command. COM_FIELD_LIST string.byte(packet) This packet contains a field list command. COM_CREATE_DB string.byte(packet) This packet contains a create db command. COM_DROP_DB string.byte(packet) This packet contains a drop db command. COM_REFRESH string.byte(packet) This packet contains a refresh command. COM_SHUTDOWN string.byte(packet) This packet contains a shutdown command. continued 767
  2. A MySQL Proxy TABLE A-5 (continued ) Constant Name Context Meaning COM_STATISTICS string.byte(packet) This packet contains a statistics command. COM_PROCESS_INFO string.byte(packet) This packet contains a process info command. COM_CONNECT string.byte(packet) This packet contains a connect command. COM_PROCESS_KILL string.byte(packet) This packet contains a kill command. COM_DEBUG string.byte(packet) This packet contains a debug command. COM_PING string.byte(packet) This packet contains a ping command. COM_TIME string.byte(packet) This packet contains a time command. COM_DELAYED_INSERT string.byte(packet) This packet contains a delayed insert command. COM_CHANGE_USER string.byte(packet) This packet contains a change user command. COM_BINLOG_DUMP string.byte(packet) This packet contains a binlog dump command. COM_TABLE_DUMP string.byte(packet) This packet contains a table dump command. COM_CONNECT_OUT string.byte(packet) This packet contains a connect out command. COM_REGISTER_SLAVE string.byte(packet) This packet contains a register slave command. COM_STMT_CLOSE string.byte(packet) This packet contains a close command. COM_STMT_EXECUTE string.byte(packet) This packet contains a execute command. COM_STMT_PREPARE string.byte(packet) This packet contains a prepare command. COM_STMT_SEND_ string.byte(packet) This packet contains a send LONG_DATA long data command. COM_STMT_RESET string.byte(packet) This packet contains a reset command. 768
  3. MySQL Proxy A TABLE A-5 (continued ) Constant Name Context Meaning COM_SET_OPTION string.byte(packet) This packet contains a set command. MYSQL_TYPE_NEWDECIMAL proxy.response. This field has a type of resultset { field DECIMAL. {{type,name}} } MYSQL_TYPE_TINY proxy.response. This field has a type of resultset { field TINY. {{type,name}} } MYSQL_TYPE_SHORT proxy.response. This field has a type of resultset { field SHORT. {{type,name}} } MYSQL_TYPE_LONG proxy.response. This field has a type of resultset { field LONG. {{type,name}} } MYSQL_TYPE_FLOAT proxy.response. This field has a type of resultset { field FLOAT. {{type,name}} } MYSQL_TYPE_DOUBLE proxy.response. This field has a type of resultset { field DOUBLE. {{type,name}} } MYSQL_TYPE_NULL proxy.response. This field has a type of resultset { field NULL. {{type,name}} } MYSQL_TYPE_TIMESTAMP proxy.response. This field has a type of resultset { field TIMESTAMP. {{type,name}} } MYSQL_TYPE_LONGLONG proxy.response. This field has a type of resultset { field LONGLONG. {{type,name}} } MYSQL_TYPE_INT24 proxy.response. This field has a type of INT. resultset { field {{type,name}} } MYSQL_TYPE_DATE proxy.response. This field has a type of resultset { field DATE. {{type,name}} } MYSQL_TYPE_TIME proxy.response. This field has a type of resultset { field TIME. {{type,name}} } continued 769
  4. A MySQL Proxy TABLE A-5 (continued ) Constant Name Context Meaning MYSQL_TYPE_DATETIME proxy.response. This field has a type of resultset { field DATETIME. {{type,name}} } MYSQL_TYPE_YEAR proxy.response. This field has a type of resultset { field YEAR. {{type,name}} } MYSQL_TYPE_NEWDATE proxy.response. This field has a type of resultset { field NEWDATE. {{type,name}} } MYSQL_TYPE_ENUM proxy.response. This field has a type of resultset { field ENUM. {{type,name}} } MYSQL_TYPE_SET proxy.response. This field has a type of SET. resultset { field {{type,name}} } MYSQL_TYPE_TINY_BLOB proxy.response. This field has a type of resultset { field TINY_BLOB. {{type,name}} } MYSQL_TYPE_ proxy.response. This field has a type of MEDIUM_BLOB resultset { field MEDIUM_BLOB. {{type,name}} } MYSQL_TYPE_LONG_BLOB proxy.response. This field has a type of resultset { field LONG_BLOB. {{type,name}} } MYSQL_TYPE_BLOB proxy.response. This field has a type of resultset { field BLOB. {{type,name}} } MYSQL_TYPE_VAR_STRING proxy.response. This field has a type of resultset { field VAR_STRING. {{type,name}} } MYSQL_TYPE_STRING proxy.response. This field has a type of resultset { field STRING. {{type,name}} } MYSQL_TYPE_GEOMETRY proxy.response. This field has a type of resultset { field GEOMETRY. {{type,name}} } MYSQL_TYPE_BIT proxy.response. This field has a type of BIT. resultset { field {{type,name}} } 770
  5. MySQL Proxy A Further layers in the internal structures are shown in Table A-6. Note that the first five rows correspond to the internal structures: ■ proxy.socket.client.server ■ proxy.socket.socket.server ■ proxy.connection.backend_ndx ■ proxy.connection.client.default_db ■ proxy.connection.client.send_queue TABLE A-6 MySQL Proxy Internal Structures Level 1 Level 2 Level 3 Level 4 Level 5 proxy socket client server socket server connection backend_ndx client default_db send_queue username address scrambled_password server thread_id mysqld_version scramble_buffer backends connected_clients address state type backends pool queue users servers queue queries type continued 771
  6. A MySQL Proxy TABLE A-6 (continued ) Level 1 Level 2 Level 3 Level 4 Level 5 query response type resultset fields rows rrrmsg packet tokens pdata text token_id token_name global config proxy lua_script The proxy.global.config structure can be extended to store user-defined variables. For example, the rw-splitting.lua sample script extends the proxy.global.config structure to include: ■ proxy.global.config.rwsplit ■ proxy.global.config.rwsplit.min_idle_connections ■ proxy.global.config.rwsplit.max_idle_connections MySQL proxy tokens The proxy tokenizer has a set of tokens that it uses to define parts of a statement. The ‘‘Proxy Tokenizer’’ section earlier showed a few such tokens, including ones for punctuation (TK_OBRACE, TK_CBRACE, TK_DOT, TK_STAR), type (TK_LITERAL, TK_STRING), and SQL keywords (TK_SQL_SELECT, TK_SQL_FROM, TK_SQL_WHERE, TK_SQL_LIKE). The tokens in mysql-proxy 0.6 .0 that are not SQL statement keywords can be found in the source code file sql-tokenizer.c and are listed in Table A-7. The SQL keyword tokens in mysql-proxy 0.6.0 are also found in the source code file sql-tokenizer.c and are listed in Table A-8. There are no examples given, because each token name is the keyword with TK_SQL_ appended. For example, the token TK_SQL_ALTER corresponds to the SQL keyword ALTER. 772
  7. MySQL Proxy A TABLE A-7 MySQL Proxy Tokens Token Name Example Punctuation TK_DOT . TK_COMMA , TK_OBRACE ( TK_CBACE ) TK_SEMICOLON ; TK_STAR * Arithmetic TK_PLUS + TK_MINUS - TK_DIV / TK_LE = TK_LT < TK_GT > TK_EQ = TK_NE != Data Types TK_STRING ‘‘foo’’ TK_INTEGER 6 TK_FLOAT 1.2 TK_FUNCTION NOW (as in SELECT NOW()) TK_COMMENT /* comment in here */ TK_LITERAL The word after SHOW (as in SHOW TABLES, SHOW VARIABLES, etc.) @ and @@ variables Database, table, and column names continued 773
  8. A MySQL Proxy TABLE A-7 (continued ) Token Name Example Functions TK_BITWISE_AND & TK_BITWISE_OR | TK_BITWISE_XOR ˆ TK_LOGICAL_AND && TK_LOGICAL_OR || TK_ASSIGN := Other TK_COMMENT_MYSQL ’A MySQL Comment’ TK_UNKNOWN []{} TABLE A-8 MySQL Proxy SQL Keyword Tokens SQL Statement Keywords TK_SQL_ACCESSIBLE TK_SQL_ACTION TK_SQL_ADD TK_SQL_ALL TK_SQL_ALTER TK_SQL_ANALYZE TK_SQL_AND TK_SQL_AS TK_SQL_ASC TK_SQL_ASENSITIVE TK_SQL_BEFORE TK_SQL_BETWEEN TK_SQL_BIGINT 774
  9. MySQL Proxy A TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_BINARY TK_SQL_BIT TK_SQL_BLOB TK_SQL_BOTH TK_SQL_BY TK_SQL_CALL TK_SQL_CASCADE TK_SQL_CASE TK_SQL_CHANGE TK_SQL_CHAR TK_SQL_CHARACTER TK_SQL_CHECK TK_SQL_COLLATE TK_SQL_COLUMN TK_SQL_CONDITION TK_SQL_CONSTRAINT TK_SQL_CONTINUE TK_SQL_CONVERT TK_SQL_CREATE TK_SQL_CROSS TK_SQL_CURRENT_DATE TK_SQL_CURRENT_TIME TK_SQL_CURRENT_TIMESTAMP TK_SQL_CURRENT_USER TK_SQL_CURSOR TK_SQL_DATABASE TK_SQL_DATABASES TK_SQL_DATE continued 775
  10. A MySQL Proxy TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_DAY_HOUR TK_SQL_DAY_MICROSECOND TK_SQL_DAY_MINUTE TK_SQL_DAY_SECOND TK_SQL_DEC TK_SQL_DECIMAL TK_SQL_DECLARE TK_SQL_DEFAULT TK_SQL_DELAYED TK_SQL_DELETE TK_SQL_DESC TK_SQL_DESCRIBE TK_SQL_DETERMINISTIC TK_SQL_DISTINCT TK_SQL_DISTINCTROW TK_SQL_DIV TK_SQL_DOUBLE TK_SQL_DROP TK_SQL_DUAL TK_SQL_EACH TK_SQL_ELSE TK_SQL_ELSEIF TK_SQL_ENCLOSED TK_SQL_ENUM TK_SQL_ESCAPED TK_SQL_EXISTS TK_SQL_EXIT TK_SQL_EXPLAIN TK_SQL_FALSE 776
  11. MySQL Proxy A TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_FETCH TK_SQL_FLOAT TK_SQL_FLOAT4 TK_SQL_FLOAT8 TK_SQL_FOR TK_SQL_FORCE TK_SQL_FOREIGN TK_SQL_FROM TK_SQL_FULLTEXT TK_SQL_GRANT TK_SQL_GROUP TK_SQL_HAVING TK_SQL_HIGH_PRIORITY TK_SQL_HOUR_MICROSECOND TK_SQL_HOUR_MINUTE TK_SQL_HOUR_SECOND TK_SQL_IF TK_SQL_IGNORE TK_SQL_IN TK_SQL_INDEX TK_SQL_INFILE TK_SQL_INNER TK_SQL_INOUT TK_SQL_INSENSITIVE TK_SQL_INSERT TK_SQL_INT TK_SQL_INT1 TK_SQL_INT2 continued 777
  12. A MySQL Proxy TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_INT3 TK_SQL_INT4 TK_SQL_INT8 TK_SQL_INTEGER TK_SQL_INTERVAL TK_SQL_INTO TK_SQL_IS TK_SQL_ITERATE TK_SQL_JOIN TK_SQL_KEY TK_SQL_KEYS TK_SQL_KILL TK_SQL_LEADING TK_SQL_LEAVE TK_SQL_LEFT TK_SQL_LIKE TK_SQL_LIMIT TK_SQL_LINEAR TK_SQL_LINES TK_SQL_LOAD TK_SQL_LOCALTIME TK_SQL_LOCALTIMESTAMP TK_SQL_LOCK TK_SQL_LONG TK_SQL_LONGBLOB TK_SQL_LONGTEXT TK_SQL_LOOP TK_SQL_LOW_PRIORITY TK_SQL_MASTER_SSL_VERIFY_SERVER_CERT 778
  13. MySQL Proxy A TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_MATCH TK_SQL_MEDIUMBLOB TK_SQL_MEDIUMINT TK_SQL_MIDDLEINT TK_SQL_MINUTE_MICROSECOND TK_SQL_MINUTE_SECOND TK_SQL_MOD TK_SQL_MODIFIES TK_SQL_NATURAL TK_SQL_NO TK_SQL_NOT TK_SQL_NO_WRITE_TO_BINLOG TK_SQL_NULL TK_SQL_NUMERIC TK_SQL_ON TK_SQL_OPTIMIZE TK_SQL_OPTION TK_SQL_OPTIONALLY TK_SQL_OR TK_SQL_ORDER TK_SQL_OUT TK_SQL_OUTER TK_SQL_OUTFILE TK_SQL_PRECISION TK_SQL_PRIMARY TK_SQL_PROCEDURE TK_SQL_PURGE TK_SQL_RANGE continued 779
  14. A MySQL Proxy TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_READ TK_SQL_READ_ONLY TK_SQL_READS TK_SQL_READ_WRITE TK_SQL_REAL TK_SQL_REFERENCES TK_SQL_REGEXP TK_SQL_RELEASE TK_SQL_RENAME TK_SQL_REPEAT TK_SQL_REPLACE TK_SQL_REQUIRE TK_SQL_RESTRICT TK_SQL_RETURN TK_SQL_REVOKE TK_SQL_RIGHT TK_SQL_RLIKE TK_SQL_SCHEMA TK_SQL_SCHEMAS TK_SQL_SECOND_MICROSECOND TK_SQL_SELECT TK_SQL_SENSITIVE TK_SQL_SEPARATOR TK_SQL_SET TK_SQL_SHOW TK_SQL_SMALLINT TK_SQL_SPATIAL TK_SQL_SPECIFIC TK_SQL_SQL 780
  15. MySQL Proxy A TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_SQL_BIG_RESULT TK_SQL_SQL_CALC_FOUND_ROWS TK_SQL_SQLEXCEPTION TK_SQL_SQL_SMALL_RESULT TK_SQL_SQLSTATE TK_SQL_SQLWARNING TK_SQL_SSL TK_SQL_STARTING TK_SQL_STRAIGHT_JOIN TK_SQL_TABLE TK_SQL_TERMINTED TK_SQL_TEXT TK_SQL_THEN TK_SQL_TIME TK_SQL_TIMESTAMP TK_SQL_TINYBLOB TK_SQL_TINYINT TK_SQL_TINYTEXT TK_SQL_TO TK_SQL_TRAILING TK_SQL_TRIGGER TK_SQL_TRUE TK_SQL_UNDO TK_SQL_UNION TK_SQL_UNIQUE TK_SQL_UNLOCK TK_SQL_UNSIGNED TK_SQL_UPDATE continued 781
  16. A MySQL Proxy TABLE A-8 (continued ) SQL Statement Keywords TK_SQL_USAGE TK_SQL_USE TK_SQL_USING TK_SQL_UTC_DATE TK_SQL_UTC_TIME TK_SQL_UTC_TIMESTAMP TK_SQL_VALUES TK_SQL_VARBINARY TK_SQL_VARCHAR TK_SQL_VARCHARACTER TK_SQL_VARYING TK_SQL_WHEN TK_SQL_WHERE TK_SQL_WHILE TK_SQL_WITH TK_SQL_WRITE TK_SQL_X509 TK_SQL_XOR TK_SQL_YEAR_MONTH TK_SQL_ZEROFILL Summary In this appendix you have learned how to use the MySQL Proxy, including: ■ Using multiple backends with MySQL Proxy ■ How MySQL Proxy’s default round-robin connection logic works ■ A brief Lua tutorial ■ How MySQL Proxy tokenizes queries ■ Query injection with MySQL Proxy ■ Reference material for more advanced MySQL Proxy scripting 782
  17. Functions and Operators T his appendix contains the functions and operators available in MySQL, including whether they are an SQL Standard or a MySQL IN THIS APPENDIX Extension. Using aggregation functions Using bitwise operators Using Aggregation Functions Compressing and encrypting data Aggregation functions take a single expression as an argument Testing, logic, and control flow but may take input from many rows; they are row-wise functions. For example, the AVG(expr) function takes a simple average Using server-level functions (the sum divided by the count): Working with data types mysql> CREATE TABLE nums (num TINYINT); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO nums (num) VALUES (1),(2),(3); Query OK, 3 rows affected (0.48 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select AVG(num) FROM nums; +----------+ | AVG(num) | +----------+ | 2.0000 | +----------+ 1 row in set (0.03 sec) Although aggregate functions work on expressions that encompass one or more rows, they only take one expression as an argument. For example, the syntax of AVG(expr) does not allow for the following: 783
  18. B Functions and Operators mysql> SELECT AVG(1,2,3); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’2,3)’ at line 1 One exception to this rule is that COUNT(DISTINCT expr) can take multiple arguments. When expr evaluates as NULL, for example when there are no rows that match, most aggre- gation functions return NULL. Functions that return 0 when expr evaluates as NULL are COUNT(*), COUNT(expr), COUNT(DISTINCT expr), BIT_OR, and BIT_XOR. Bug here is one function that returns an unexpected result: BIT_AND(NULL): T mysql> SELECT BIT_AND(NULL); +----------------------+ | BIT_AND(NULL) | +----------------------+ | 18446744073709551615 | +----------------------+ 1 row in set (0.00 sec) For the curious, 18446744073709551615 is a 63-bit string where each bit is 1. This has been reported as MySQL bug 37754 and can be seen at: http://bugs.mysql.com/bug.php? id=37754. Table B-1 lists the aggregate functions and a brief description of each one. Using Bitwise Operators MySQL extends standard SQL with special functions for bits. Bitwise operators implicitly cast arguments to UNSIGNED BIGINT. This has the most noticeable effect on bit inversion (∼): mysql> SELECT 5,∼5,BIN(5),BIN(∼5)\G *************************** 1. row *************************** 5: 5 ∼5: 18446744073709551610 BIN(5): 101 BIN(∼5): 11111111111111111111111111111111111111111111111111111111111 11010 1 row in set (0.00 sec) Aggregation functions for bits are listed in Table B-1. 784
  19. Functions and Operators B TABLE B-1 Aggregation Functions Function Syntax Standard Description SQL? AVG(expr) Yes Simple average; SUM/COUNT. BIT_AND(expr) No Bitwise AND; returns true if all rows are true and false if any rows are false. BIT_OR(expr) No Bitwise OR; returns true if any row is true; returns false if all rows are false. BIT_XOR(expr) No Bitwise XOR; a pair where exactly one value is true returns true; all other pairs will return false. More than 2 rows are done commutatively. COUNT(expr) Yes Returns a count of all non-NULL values of expr. COUNT(*) Yes COUNT(*) returns a count of all rows. COUNT(DISTINCT expr) Yes COUNT(DISTINCT expr) returns a count of how many rows contain different values for expr. GROUP_CONCAT(expr) No Concatenates non-NULL values, separated by a comma by default. MAX(expr) Yes Returns the maximum value of expr. MIN(expr) Yes Returns the minimum value of expr. STD(expr) No Nonstandard SQL equivalent to STDDEV_POP(expr). STDDEV_POP(expr) Yes Population standard deviation. Same as SQRT(VAR_POP(expr)). STDDEV_SAMP(expr) Yes Sample standard deviation. Same as SQRT(VAR_SAMP(expr)). STDDEV(expr) No Nonstandard SQL equivalent to STDDEV_POP(expr). SUM(expr) Yes Returns the sum of all values of expr. VAR_POP(expr) Yes Population standard variance. This is calculated by taking the average of the square of the differences between each value and the average value. VAR_SAMP(expr) Yes Population standard variance. This is calculated by taking the sum of the square of the differences between each value and the average value, and dividing by the number of values (rows) minus one. VARIANCE(expr) No Nonstandard SQL equivalent to VAR_POP(expr). 785
  20. B Functions and Operators Table B-2 lists the bit functions and their descriptions. TABLE B-2 Bit Functions Function Standard Description Syntax SQL? & No Bitwise AND, returns true when both bit positions are true ∼ No Bit inversion (unary operator) | No Bitwise OR, returns true when either or both bit positions are true ˆ No Bitwise XOR, returns true when exactly one bit position is true > No Shifts to the right BIT_COUNT No Returns the number of bits that are set Combining multiple statements Much like basic arithmetic operators (such as + - * / ), these bitwise operators can be combined into longer statements, which are then parsed using precedence rules. Precedence from highest to least is shown in Table B-3. TABLE B-3 Bit Operator Precedence Operator(s) Level of Precedence ∼ Highest ˆ > & | Lowest Let’s walk through an example: mysql> SELECT 5 | 2 ˆ 7
Đồng bộ tài khoản