后台组件编程专栏
二、后台组件编程专栏
0.项目仓库
1.MySQL基本操作与编程
1.1 MySQL基础逻辑框架
1.2 基本操作
-
安装
sudo apt-get install mysql-server mysql-client -y -
启动、重启、关闭
sudo /etc/init.d/mysql start|stop|restart -
连接
mysql -h127.0.0.1 -uroot -p #root用户默认密码为空 -
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’问题解决
# 查看/etc/mysql/debian.cnf sudo vim /etc/mysql/debian.cnf ########################################################### # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint # 记住这两项 password = 5VRSokq1P0uQ2S39 # 记住这两项 socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = 5VRSokq1P0uQ2S39 socket = /var/run/mysqld/mysqld.sock ########################################################### mysql -udebian-sys-maint -p5VRSokq1P0uQ2S39 # 登录MySQL成功后 select user, plugin from mysql.user; update mysql.user set authentication_string=PASSWORD('123'), plugin='mysql_native_password' where user='root'; flush privileges; -
数据库操作
# 创建数据库 create database `test` default character set utf8; # 使用数据库 use test; # 创建表 create table user_info ( id int not null auto_increment, `name` varchar(20), `title` varchar(20), primary key(id) )engine = InnoDB charset = utf8; # 增删查改 insert into user_info(`name`, `title`)values('gongluck', 'test'); select * from user_info; update user_info set `name`='test' where id = 1; delete from user_info where id = 1; -
用户操作
# 创建用户 #CREATE USER username@host IDENTIFIED BY password; CREATE USER 'gongluck'@'%' IDENTIFIED BY '123'; # 授权 #GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; #privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'gongluck'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'gongluck'@'%'; FLUSH PRIVILEGES;
1.3 libmysql编程环境
-
安装
sudo apt-get install libmysqlclient-dev -y -
头文件（/usr/include/mysql/mysql.h）
/* Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License, version 2.0, as published by the Free Software Foundation. This program is also distributed with certain software (including but not limited to OpenSSL) that is licensed under separate terms, as designated in a particular file or component or in included license documentation. The authors of MySQL hereby grant you an additional permission to link the program and your derivative works with the separately licensed software that they have included with MySQL. Without limiting anything contained in the foregoing, this file, which is part of C Driver for MySQL (Connector/C), is also subject to the Universal FOSS Exception, version 1.0, a copy of which can be found at http://oss.oracle.com/licenses/universal-foss-exception. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License, version 2.0, for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ /* This file defines the client API to MySQL and also the ABI of the dynamically linked libmysqlclient. The ABI should never be changed in a released product of MySQL, thus you need to take great care when changing the file. In case the file is changed so the ABI is broken, you must also update the SHARED_LIB_MAJOR_VERSION in cmake/mysql_version.cmake */ #ifndef _mysql_h #define _mysql_h #ifdef __cplusplus extern "C" { #endif #ifndef MY_GLOBAL_INCLUDED /* If not standard header */ #ifndef MYSQL_ABI_CHECK #include <sys/types.h> #endif typedef char my_bool; #if !defined(_WIN32) #define STDCALL #else #define STDCALL __stdcall #endif #ifndef my_socket_defined #ifdef _WIN32 #include <windows.h> #ifdef WIN32_LEAN_AND_MEAN #include <winsock2.h> #endif #define my_socket SOCKET #else typedef int my_socket; #endif /* _WIN32 */ #endif /* my_socket_defined */ #endif /* MY_GLOBAL_INCLUDED */ #include "mysql_version.h" #include "mysql_com.h" #include "mysql_time.h" #include "my_list.h" /* for LISTs used in 'MYSQL' and 'MYSQL_STMT' */ /* Include declarations of plug-in API */ #include "mysql/client_plugin.h" extern unsigned int mysql_port; extern char *mysql_unix_port; #define CLIENT_NET_READ_TIMEOUT 365*24*3600 /* Timeout on read */ #define CLIENT_NET_WRITE_TIMEOUT 365*24*3600 /* Timeout on write */ #define IS_PRI_KEY(n) ((n) & PRI_KEY_FLAG) #define IS_NOT_NULL(n) ((n) & NOT_NULL_FLAG) #define IS_BLOB(n) ((n) & BLOB_FLAG) /** Returns true if the value is a number which does not need quotes for the sql_lex.cc parser to parse correctly. */ #define IS_NUM(t) (((t) <= MYSQL_TYPE_INT24 && (t) != MYSQL_TYPE_TIMESTAMP) || (t) == MYSQL_TYPE_YEAR || (t) == MYSQL_TYPE_NEWDECIMAL) #define IS_LONGDATA(t) ((t) >= MYSQL_TYPE_TINY_BLOB && (t) <= MYSQL_TYPE_STRING) typedef struct st_mysql_field { char *name; /* Name of column */ char *org_name; /* Original column name, if an alias */ char *table; /* Table of column if column was a field */ char *org_table; /* Org table name, if table was an alias */ char *db; /* Database for table */ char *catalog; /* Catalog for table */ char *def; /* Default value (set by mysql_list_fields) */ unsigned long length; /* Width of column (create length) */ unsigned long max_length; /* Max width for selected set */ unsigned int name_length; unsigned int org_name_length; unsigned int table_length; unsigned int org_table_length; unsigned int db_length; unsigned int catalog_length; unsigned int def_length; unsigned int flags; /* Div flags */ unsigned int decimals; /* Number of decimals in field */ unsigned int charsetnr; /* Character set */ enum enum_field_types type; /* Type of field. See mysql_com.h for types */ void *extension; } MYSQL_FIELD; typedef char **MYSQL_ROW; /* return data as array of strings */ typedef unsigned int MYSQL_FIELD_OFFSET; /* offset to current field */ #ifndef MY_GLOBAL_INCLUDED #if defined (_WIN32) typedef unsigned __int64 my_ulonglong; #else typedef unsigned long long my_ulonglong; #endif #endif #include "typelib.h" #define MYSQL_COUNT_ERROR (~(my_ulonglong) 0) /* backward compatibility define - to be removed eventually */ #define ER_WARN_DATA_TRUNCATED WARN_DATA_TRUNCATED typedef struct st_mysql_rows { struct st_mysql_rows *next; /* list of rows */ MYSQL_ROW data; unsigned long length; } MYSQL_ROWS; typedef MYSQL_ROWS *MYSQL_ROW_OFFSET; /* offset to current row */ #include "my_alloc.h" typedef struct embedded_query_result EMBEDDED_QUERY_RESULT; typedef struct st_mysql_data { MYSQL_ROWS *data; struct embedded_query_result *embedded_info; MEM_ROOT alloc; my_ulonglong rows; unsigned int fields; /* extra info for embedded library */ void *extension; } MYSQL_DATA; enum mysql_option { MYSQL_OPT_CONNECT_TIMEOUT, MYSQL_OPT_COMPRESS, MYSQL_OPT_NAMED_PIPE, MYSQL_INIT_COMMAND, MYSQL_READ_DEFAULT_FILE, MYSQL_READ_DEFAULT_GROUP, MYSQL_SET_CHARSET_DIR, MYSQL_SET_CHARSET_NAME, MYSQL_OPT_LOCAL_INFILE, MYSQL_OPT_PROTOCOL, MYSQL_SHARED_MEMORY_BASE_NAME, MYSQL_OPT_READ_TIMEOUT, MYSQL_OPT_WRITE_TIMEOUT, MYSQL_OPT_USE_RESULT, MYSQL_OPT_USE_REMOTE_CONNECTION, MYSQL_OPT_USE_EMBEDDED_CONNECTION, MYSQL_OPT_GUESS_CONNECTION, MYSQL_SET_CLIENT_IP, MYSQL_SECURE_AUTH, MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_RECONNECT, MYSQL_OPT_SSL_VERIFY_SERVER_CERT, MYSQL_PLUGIN_DIR, MYSQL_DEFAULT_AUTH, MYSQL_OPT_BIND, MYSQL_OPT_SSL_KEY, MYSQL_OPT_SSL_CERT, MYSQL_OPT_SSL_CA, MYSQL_OPT_SSL_CAPATH, MYSQL_OPT_SSL_CIPHER, MYSQL_OPT_SSL_CRL, MYSQL_OPT_SSL_CRLPATH, MYSQL_OPT_CONNECT_ATTR_RESET, MYSQL_OPT_CONNECT_ATTR_ADD, MYSQL_OPT_CONNECT_ATTR_DELETE, MYSQL_SERVER_PUBLIC_KEY, MYSQL_ENABLE_CLEARTEXT_PLUGIN, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, MYSQL_OPT_SSL_ENFORCE, MYSQL_OPT_MAX_ALLOWED_PACKET, MYSQL_OPT_NET_BUFFER_LENGTH, MYSQL_OPT_TLS_VERSION, MYSQL_OPT_SSL_MODE, MYSQL_OPT_GET_SERVER_PUBLIC_KEY }; /** @todo remove the "extension", move st_mysql_options completely out of mysql.h */ struct st_mysql_options_extention; struct st_mysql_options { unsigned int connect_timeout, read_timeout, write_timeout; unsigned int port, protocol; unsigned long client_flag; char *host,*user,*password,*unix_socket,*db; struct st_dynamic_array *init_commands; char *my_cnf_file,*my_cnf_group, *charset_dir, *charset_name; char *ssl_key; /* PEM key file */ char *ssl_cert; /* PEM cert file */ char *ssl_ca; /* PEM CA file */ char *ssl_capath; /* PEM directory of CA-s? */ char *ssl_cipher; /* cipher to use */ char *shared_memory_base_name; unsigned long max_allowed_packet; my_bool use_ssl; /* Deprecated ! Former use_ssl */ my_bool compress,named_pipe; my_bool unused1; my_bool unused2; my_bool unused3; my_bool unused4; enum mysql_option methods_to_use; union { /* The ip/hostname to use when authenticating client against embedded server built with grant tables - only used in embedded server */ char *client_ip; /* The local address to bind when connecting to remote server - not used in embedded server */ char *bind_address; } ci; my_bool unused5; /* 0 - never report, 1 - always report (default) */ my_bool report_data_truncation; /* function pointers for local infile support */ int (*local_infile_init)(void **, const char *, void *); int (*local_infile_read)(void *, char *, unsigned int); void (*local_infile_end)(void *); int (*local_infile_error)(void *, char *, unsigned int); void *local_infile_userdata; struct st_mysql_options_extention *extension; }; enum mysql_status { MYSQL_STATUS_READY, MYSQL_STATUS_GET_RESULT, MYSQL_STATUS_USE_RESULT, MYSQL_STATUS_STATEMENT_GET_RESULT }; enum mysql_protocol_type { MYSQL_PROTOCOL_DEFAULT, MYSQL_PROTOCOL_TCP, MYSQL_PROTOCOL_SOCKET, MYSQL_PROTOCOL_PIPE, MYSQL_PROTOCOL_MEMORY }; enum mysql_ssl_mode { SSL_MODE_DISABLED= 1, SSL_MODE_PREFERRED, SSL_MODE_REQUIRED, SSL_MODE_VERIFY_CA, SSL_MODE_VERIFY_IDENTITY }; typedef struct character_set { unsigned int number; /* character set number */ unsigned int state; /* character set state */ const char *csname; /* collation name */ const char *name; /* character set name */ const char *comment; /* comment */ const char *dir; /* character set directory */ unsigned int mbminlen; /* min. length for multibyte strings */ unsigned int mbmaxlen; /* max. length for multibyte strings */ } MY_CHARSET_INFO; struct st_mysql_methods; struct st_mysql_stmt; typedef struct st_mysql { NET net; /* Communication parameters */ unsigned char *connector_fd; /* ConnectorFd for SSL */ char *host,*user,*passwd,*unix_socket,*server_version,*host_info; char *info, *db; struct charset_info_st *charset; MYSQL_FIELD *fields; MEM_ROOT field_alloc; my_ulonglong affected_rows; my_ulonglong insert_id; /* id if insert on table with NEXTNR */ my_ulonglong extra_info; /* Not used */ unsigned long thread_id; /* Id for connection in server */ unsigned long packet_length; unsigned int port; unsigned long client_flag,server_capabilities; unsigned int protocol_version; unsigned int field_count; unsigned int server_status; unsigned int server_language; unsigned int warning_count; struct st_mysql_options options; enum mysql_status status; my_bool free_me; /* If free in mysql_close */ my_bool reconnect; /* set to 1 if automatic reconnect */ /* session-wide random string */ char scramble[SCRAMBLE_LENGTH+1]; my_bool unused1; void *unused2, *unused3, *unused4, *unused5; LIST *stmts; /* list of all statements */ const struct st_mysql_methods *methods; void *thd; /* Points to boolean flag in MYSQL_RES or MYSQL_STMT. We set this flag from mysql_stmt_close if close had to cancel result set of this object. */ my_bool *unbuffered_fetch_owner; /* needed for embedded server - no net buffer to store the 'info' */ char *info_buffer; void *extension; } MYSQL; typedef struct st_mysql_res { my_ulonglong row_count; MYSQL_FIELD *fields; MYSQL_DATA *data; MYSQL_ROWS *data_cursor; unsigned long *lengths; /* column lengths of current row */ MYSQL *handle; /* for unbuffered reads */ const struct st_mysql_methods *methods; MYSQL_ROW row; /* If unbuffered read */ MYSQL_ROW current_row; /* buffer to current row */ MEM_ROOT field_alloc; unsigned int field_count, current_field; my_bool eof; /* Used by mysql_fetch_row */ /* mysql_stmt_close() had to cancel this result */ my_bool unbuffered_fetch_cancelled; void *extension; } MYSQL_RES; #if !defined(MYSQL_SERVER) && !defined(MYSQL_CLIENT) #define MYSQL_CLIENT #endif /* Set up and bring down the server; to ensure that applications will work when linked against either the standard client library or the embedded server library, these functions should be called. */ int STDCALL mysql_server_init(int argc, char **argv, char **groups); void STDCALL mysql_server_end(void); /* mysql_server_init/end need to be called when using libmysqld or libmysqlclient (exactly, mysql_server_init() is called by mysql_init() so you don't need to call it explicitely; but you need to call mysql_server_end() to free memory). The names are a bit misleading (mysql_SERVER* to be used when using libmysqlCLIENT). So we add more general names which suit well whether you're using libmysqld or libmysqlclient. We intend to promote these aliases over the mysql_server* ones. */ #define mysql_library_init mysql_server_init #define mysql_library_end mysql_server_end /* Set up and bring down a thread; these function should be called for each thread in an application which opens at least one MySQL connection. All uses of the connection(s) should be between these function calls. */ my_bool STDCALL mysql_thread_init(void); void STDCALL mysql_thread_end(void); /* Functions to get information from the MYSQL and MYSQL_RES structures Should definitely be used if one uses shared libraries. */ my_ulonglong STDCALL mysql_num_rows(MYSQL_RES *res); unsigned int STDCALL mysql_num_fields(MYSQL_RES *res); my_bool STDCALL mysql_eof(MYSQL_RES *res); MYSQL_FIELD *STDCALL mysql_fetch_field_direct(MYSQL_RES *res, unsigned int fieldnr); MYSQL_FIELD * STDCALL mysql_fetch_fields(MYSQL_RES *res); MYSQL_ROW_OFFSET STDCALL mysql_row_tell(MYSQL_RES *res); MYSQL_FIELD_OFFSET STDCALL mysql_field_tell(MYSQL_RES *res); unsigned int STDCALL mysql_field_count(MYSQL *mysql); my_ulonglong STDCALL mysql_affected_rows(MYSQL *mysql); my_ulonglong STDCALL mysql_insert_id(MYSQL *mysql); unsigned int STDCALL mysql_errno(MYSQL *mysql); const char * STDCALL mysql_error(MYSQL *mysql); const char *STDCALL mysql_sqlstate(MYSQL *mysql); unsigned int STDCALL mysql_warning_count(MYSQL *mysql); const char * STDCALL mysql_info(MYSQL *mysql); unsigned long STDCALL mysql_thread_id(MYSQL *mysql); const char * STDCALL mysql_character_set_name(MYSQL *mysql); int STDCALL mysql_set_character_set(MYSQL *mysql, const char *csname); MYSQL * STDCALL mysql_init(MYSQL *mysql); my_bool STDCALL mysql_ssl_set(MYSQL *mysql, const char *key, const char *cert, const char *ca, const char *capath, const char *cipher); const char * STDCALL mysql_get_ssl_cipher(MYSQL *mysql); my_bool STDCALL mysql_change_user(MYSQL *mysql, const char *user, const char *passwd, const char *db); MYSQL * STDCALL mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long clientflag); int STDCALL mysql_select_db(MYSQL *mysql, const char *db); int STDCALL mysql_query(MYSQL *mysql, const char *q); int STDCALL mysql_send_query(MYSQL *mysql, const char *q, unsigned long length); int STDCALL mysql_real_query(MYSQL *mysql, const char *q, unsigned long length); MYSQL_RES * STDCALL mysql_store_result(MYSQL *mysql); MYSQL_RES * STDCALL mysql_use_result(MYSQL *mysql); void STDCALL mysql_get_character_set_info(MYSQL *mysql, MY_CHARSET_INFO *charset); int STDCALL mysql_session_track_get_first(MYSQL *mysql, enum enum_session_state_type type, const char **data, size_t *length); int STDCALL mysql_session_track_get_next(MYSQL *mysql, enum enum_session_state_type type, const char **data, size_t *length); /* local infile support */ #define LOCAL_INFILE_ERROR_LEN 512 void mysql_set_local_infile_handler(MYSQL *mysql, int (*local_infile_init)(void **, const char *, void *), int (*local_infile_read)(void *, char *, unsigned int), void (*local_infile_end)(void *), int (*local_infile_error)(void *, char*, unsigned int), void *); void mysql_set_local_infile_default(MYSQL *mysql); int STDCALL mysql_shutdown(MYSQL *mysql, enum mysql_enum_shutdown_level shutdown_level); int STDCALL mysql_dump_debug_info(MYSQL *mysql); int STDCALL mysql_refresh(MYSQL *mysql, unsigned int refresh_options); int STDCALL mysql_kill(MYSQL *mysql,unsigned long pid); int STDCALL mysql_set_server_option(MYSQL *mysql, enum enum_mysql_set_option option); int STDCALL mysql_ping(MYSQL *mysql); const char * STDCALL mysql_stat(MYSQL *mysql); const char * STDCALL mysql_get_server_info(MYSQL *mysql); const char * STDCALL mysql_get_client_info(void); unsigned long STDCALL mysql_get_client_version(void); const char * STDCALL mysql_get_host_info(MYSQL *mysql); unsigned long STDCALL mysql_get_server_version(MYSQL *mysql); unsigned int STDCALL mysql_get_proto_info(MYSQL *mysql); MYSQL_RES * STDCALL mysql_list_dbs(MYSQL *mysql,const char *wild); MYSQL_RES * STDCALL mysql_list_tables(MYSQL *mysql,const char *wild); MYSQL_RES * STDCALL mysql_list_processes(MYSQL *mysql); int STDCALL mysql_options(MYSQL *mysql,enum mysql_option option, const void *arg); int STDCALL mysql_options4(MYSQL *mysql,enum mysql_option option, const void *arg1, const void *arg2); int STDCALL mysql_get_option(MYSQL *mysql, enum mysql_option option, const void *arg); void STDCALL mysql_free_result(MYSQL_RES *result); void STDCALL mysql_data_seek(MYSQL_RES *result, my_ulonglong offset); MYSQL_ROW_OFFSET STDCALL mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset); MYSQL_FIELD_OFFSET STDCALL mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset); MYSQL_ROW STDCALL mysql_fetch_row(MYSQL_RES *result); unsigned long * STDCALL mysql_fetch_lengths(MYSQL_RES *result); MYSQL_FIELD * STDCALL mysql_fetch_field(MYSQL_RES *result); MYSQL_RES * STDCALL mysql_list_fields(MYSQL *mysql, const char *table, const char *wild); unsigned long STDCALL mysql_escape_string(char *to,const char *from, unsigned long from_length); unsigned long STDCALL mysql_hex_string(char *to,const char *from, unsigned long from_length); unsigned long STDCALL mysql_real_escape_string(MYSQL *mysql, char *to,const char *from, unsigned long length); unsigned long STDCALL mysql_real_escape_string_quote(MYSQL *mysql, char *to, const char *from, unsigned long length, char quote); void STDCALL mysql_debug(const char *debug); void STDCALL myodbc_remove_escape(MYSQL *mysql,char *name); unsigned int STDCALL mysql_thread_safe(void); my_bool STDCALL mysql_embedded(void); my_bool STDCALL mysql_read_query_result(MYSQL *mysql); int STDCALL mysql_reset_connection(MYSQL *mysql); /* The following definitions are added for the enhanced client-server protocol */ /* statement state */ enum enum_mysql_stmt_state { MYSQL_STMT_INIT_DONE= 1, MYSQL_STMT_PREPARE_DONE, MYSQL_STMT_EXECUTE_DONE, MYSQL_STMT_FETCH_DONE }; /* This structure is used to define bind information, and internally by the client library. Public members with their descriptions are listed below (conventionally `On input' refers to the binds given to mysql_stmt_bind_param, `On output' refers to the binds given to mysql_stmt_bind_result): buffer_type - One of the MYSQL_* types, used to describe the host language type of buffer. On output: if column type is different from buffer_type, column value is automatically converted to buffer_type before it is stored in the buffer. buffer - On input: points to the buffer with input data. On output: points to the buffer capable to store output data. The type of memory pointed by buffer must correspond to buffer_type. See the correspondence table in the comment to mysql_stmt_bind_param. The two above members are mandatory for any kind of bind. buffer_length - the length of the buffer. You don't have to set it for any fixed length buffer: float, double, int, etc. It must be set however for variable-length types, such as BLOBs or STRINGs. length - On input: in case when lengths of input values are different for each execute, you can set this to point at a variable containining value length. This way the value length can be different in each execute. If length is not NULL, buffer_length is not used. Note, length can even point at buffer_length if you keep bind structures around while fetching: this way you can change buffer_length before each execution, everything will work ok. On output: if length is set, mysql_stmt_fetch will write column length into it. is_null - On input: points to a boolean variable that should be set to TRUE for NULL values. This member is useful only if your data may be NULL in some but not all cases. If your data is never NULL, is_null should be set to 0. If your data is always NULL, set buffer_type to MYSQL_TYPE_NULL, and is_null will not be used. is_unsigned - On input: used to signify that values provided for one of numeric types are unsigned. On output describes signedness of the output buffer. If, taking into account is_unsigned flag, column data is out of range of the output buffer, data for this column is regarded truncated. Note that this has no correspondence to the sign of result set column, if you need to find it out use mysql_stmt_result_metadata. error - where to write a truncation error if it is present. possible error value is: 0 no truncation 1 value is out of range or buffer is too small Please note that MYSQL_BIND also has internals members. */ typedef struct st_mysql_bind { unsigned long *length; /* output length pointer */ my_bool *is_null; /* Pointer to null indicator */ void *buffer; /* buffer to get/put data */ /* set this if you want to track data truncations happened during fetch */ my_bool *error; unsigned char *row_ptr; /* for the current data position */ void (*store_param_func)(NET *net, struct st_mysql_bind *param); void (*fetch_result)(struct st_mysql_bind *, MYSQL_FIELD *, unsigned char **row); void (*skip_result)(struct st_mysql_bind *, MYSQL_FIELD *, unsigned char **row); /* output buffer length, must be set when fetching str/binary */ unsigned long buffer_length; unsigned long offset; /* offset position for char/binary fetch */ unsigned long length_value; /* Used if length is 0 */ unsigned int param_number; /* For null count and error messages */ unsigned int pack_length; /* Internal length for packed data */ enum enum_field_types buffer_type; /* buffer type */ my_bool error_value; /* used if error is 0 */ my_bool is_unsigned; /* set if integer type is unsigned */ my_bool long_data_used; /* If used with mysql_send_long_data */ my_bool is_null_value; /* Used if is_null is 0 */ void *extension; } MYSQL_BIND; struct st_mysql_stmt_extension; /* statement handler */ typedef struct st_mysql_stmt { MEM_ROOT mem_root; /* root allocations */ LIST list; /* list to keep track of all stmts */ MYSQL *mysql; /* connection handle */ MYSQL_BIND *params; /* input parameters */ MYSQL_BIND *bind; /* output parameters */ MYSQL_FIELD *fields; /* result set metadata */ MYSQL_DATA result; /* cached result set */ MYSQL_ROWS *data_cursor; /* current row in cached result */ /* mysql_stmt_fetch() calls this function to fetch one row (it's different for buffered, unbuffered and cursor fetch). */ int (*read_row_func)(struct st_mysql_stmt *stmt, unsigned char **row); /* copy of mysql->affected_rows after statement execution */ my_ulonglong affected_rows; my_ulonglong insert_id; /* copy of mysql->insert_id */ unsigned long stmt_id; /* Id for prepared statement */ unsigned long flags; /* i.e. type of cursor to open */ unsigned long prefetch_rows; /* number of rows per one COM_FETCH */ /* Copied from mysql->server_status after execute/fetch to know server-side cursor status for this statement. */ unsigned int server_status; unsigned int last_errno; /* error code */ unsigned int param_count; /* input parameter count */ unsigned int field_count; /* number of columns in result set */ enum enum_mysql_stmt_state state; /* statement state */ char last_error[MYSQL_ERRMSG_SIZE]; /* error message */ char sqlstate[SQLSTATE_LENGTH+1]; /* Types of input parameters should be sent to server */ my_bool send_types_to_server; my_bool bind_param_done; /* input buffers were supplied */ unsigned char bind_result_done; /* output buffers were supplied */ /* mysql_stmt_close() had to cancel this result */ my_bool unbuffered_fetch_cancelled; /* Is set to true if we need to calculate field->max_length for metadata fields when doing mysql_stmt_store_result. */ my_bool update_max_length; struct st_mysql_stmt_extension *extension; } MYSQL_STMT; enum enum_stmt_attr_type { /* When doing mysql_stmt_store_result calculate max_length attribute of statement metadata. This is to be consistent with the old API, where this was done automatically. In the new API we do that only by request because it slows down mysql_stmt_store_result sufficiently. */ STMT_ATTR_UPDATE_MAX_LENGTH, /* unsigned long with combination of cursor flags (read only, for update, etc) */ STMT_ATTR_CURSOR_TYPE, /* Amount of rows to retrieve from server per one fetch if using cursors. Accepts unsigned long attribute in the range 1 - ulong_max */ STMT_ATTR_PREFETCH_ROWS }; MYSQL_STMT * STDCALL mysql_stmt_init(MYSQL *mysql); int STDCALL mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length); int STDCALL mysql_stmt_execute(MYSQL_STMT *stmt); int STDCALL mysql_stmt_fetch(MYSQL_STMT *stmt); int STDCALL mysql_stmt_fetch_column(MYSQL_STMT *stmt, MYSQL_BIND *bind_arg, unsigned int column, unsigned long offset); int STDCALL mysql_stmt_store_result(MYSQL_STMT *stmt); unsigned long STDCALL mysql_stmt_param_count(MYSQL_STMT * stmt); my_bool STDCALL mysql_stmt_attr_set(MYSQL_STMT *stmt, enum enum_stmt_attr_type attr_type, const void *attr); my_bool STDCALL mysql_stmt_attr_get(MYSQL_STMT *stmt, enum enum_stmt_attr_type attr_type, void *attr); my_bool STDCALL mysql_stmt_bind_param(MYSQL_STMT * stmt, MYSQL_BIND * bnd); my_bool STDCALL mysql_stmt_bind_result(MYSQL_STMT * stmt, MYSQL_BIND * bnd); my_bool STDCALL mysql_stmt_close(MYSQL_STMT * stmt); my_bool STDCALL mysql_stmt_reset(MYSQL_STMT * stmt); my_bool STDCALL mysql_stmt_free_result(MYSQL_STMT *stmt); my_bool STDCALL mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int param_number, const char *data, unsigned long length); MYSQL_RES *STDCALL mysql_stmt_result_metadata(MYSQL_STMT *stmt); MYSQL_RES *STDCALL mysql_stmt_param_metadata(MYSQL_STMT *stmt); unsigned int STDCALL mysql_stmt_errno(MYSQL_STMT * stmt); const char *STDCALL mysql_stmt_error(MYSQL_STMT * stmt); const char *STDCALL mysql_stmt_sqlstate(MYSQL_STMT * stmt); MYSQL_ROW_OFFSET STDCALL mysql_stmt_row_seek(MYSQL_STMT *stmt, MYSQL_ROW_OFFSET offset); MYSQL_ROW_OFFSET STDCALL mysql_stmt_row_tell(MYSQL_STMT *stmt); void STDCALL mysql_stmt_data_seek(MYSQL_STMT *stmt, my_ulonglong offset); my_ulonglong STDCALL mysql_stmt_num_rows(MYSQL_STMT *stmt); my_ulonglong STDCALL mysql_stmt_affected_rows(MYSQL_STMT *stmt); my_ulonglong STDCALL mysql_stmt_insert_id(MYSQL_STMT *stmt); unsigned int STDCALL mysql_stmt_field_count(MYSQL_STMT *stmt); my_bool STDCALL mysql_commit(MYSQL * mysql); my_bool STDCALL mysql_rollback(MYSQL * mysql); my_bool STDCALL mysql_autocommit(MYSQL * mysql, my_bool auto_mode); my_bool STDCALL mysql_more_results(MYSQL *mysql); int STDCALL mysql_next_result(MYSQL *mysql); int STDCALL mysql_stmt_next_result(MYSQL_STMT *stmt); void STDCALL mysql_close(MYSQL *sock); /* status return codes */ #define MYSQL_NO_DATA 100 #define MYSQL_DATA_TRUNCATED 101 #define mysql_reload(mysql) mysql_refresh((mysql),REFRESH_GRANT) #define HAVE_MYSQL_REAL_CONNECT #ifdef __cplusplus } #endif #endif /* _mysql_h */ -
MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; char server[] = "127.0.0.1"; char user[] = "gongluck"; char password[] = "123"; char database[] = "test"; conn = mysql_init(NULL); if (mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { printf("%s\n", mysql_error(conn)); } char sql[128] = {0}; sprintf(sql, "insert into user_info(`name`,title)values('gongluck', 'test');"); if (mysql_query(conn, sql)) { printf("%s\n", mysql_error(conn)); } if (mysql_query(conn, "select * from user_info")) { printf("%s\n", mysql_error(conn)); } res = mysql_use_result(conn); while ((row = mysql_fetch_row(res)) != NULL) { printf("%s\t", row[0]); printf("%s\t", row[1]); printf("%s\n", row[2]); } mysql_free_result(res); mysql_close(conn);
2.MySQL事务、索引、存储引擎
2.1 MySQL事务
-
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。
-
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
-
事务用来管理insert,update,delete语句。
-
事务四大特征：
- 原子性（Atomicity，或称不可分割性）
- 一致性（Consistency）
- 隔离性（Isolation，又称独立性）
- 持久性（Durability）
-
按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
- 行级锁
- 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。
- 行级锁能大大减少数据库操作的冲突。
- 其加锁粒度最小，但加锁的开销也最大。
- 行级锁分为共享锁和排他锁。
- 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 表级锁
- 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL引擎支持。
- 最常使用的MYISAM与INNODB都支持表级锁定。
- 表级锁定分为表共享读锁（共享锁）与表独占写锁 （排他锁）。
- 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
- 页级锁
- 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。
- 表级锁速度快，但冲突多，行级冲突少，但速度慢。 所以取了折衷的页级，一次锁定相邻的一组记录。
- BDB支持页级锁
- 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
- 行级锁
2.2 MySQL索引
-
根据存储分类
- B-树索引
- 哈希索引
-
根据用途分类
- 普通索引
- 唯一性索引
- 主键索引
- 空间索引
- 全文索引
-
索引的实现原理
- MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
- InnoDB也使用B+Tree作为索引结构，InnoDB的数据文件本身就是索引文件。
2.3 MySQL存储引擎
-
创建表指定存储引擎（默认InnoDB）
CREATE TABLE `test`(`id` int(11) NOT NULL AUTO_INCREMENT) ENGINE = InnoDB; -
修改数据表命令
alter table test engine = MyISAM; -
InnoDB
-
所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上
-
InnoDB存储引擎在磁盘中存放的对应的表的磁盘文件有*.frm*、*.ibd*这两个文件
-
frm文件是存放表结构，表的定义信息
-
ibd文件是存放表中的数据、索引信息
-
-
MyISAM
-
主键索引的叶子节点只存放数据在物理磁盘上的指针
-
MyISAM索引文件在数据库中存放的对应表的磁盘文件有*.frm*、*.MYD*、*.MYI*结尾的三个文件
-
frm文件是存放的表结构，表的定义信息
-
MYD文件是存放着表中的数据
-
MYI文件存放着表的索引信息
-
3.Nginx反向代理负载均衡配置
3.1 安装编译
# 安装依赖
sudo apt-get update
sudo apt-get install build-essential libtool -y
sudo apt-get install libpcre3 libpcre3-dev -y
sudo apt-get install zlib1g-dev -y
sudo apt-get install openssl -y
#下载nginx
wget http://nginx.org/download/nginx-1.19.0.tar.gz
tar zxvf nginx-1.19.0.tar.gz
cd nginx-1.19.0/
# 配置
./configure --prefix=/usr/local/nginx --with-http_ssl_module --with-http_realip_module --with-http_addition_module --with-http_gzip_static_module --with-http_secure_link_module --with-http_stub_status_module --with-stream --with-pcre #--with-zlib --with-openssl
# 编译
make -j 8
# 安装
sudo make install
# 启动
sudo /usr/local/nginx/sbin/nginx -c test.conf
# 停止
sudo /usr/local/nginx/sbin/nginx -s stop
# 重新加载配置文件
sudo /usr/local/nginx/sbin/nginx -s reload
3.2 配置文件
worker_processes 4;#工作进程数
events {
worker_connections 1024;#单个工作进程可以允许同时建立外部连接的数量
}
#设定http服务器，利用它的反向代理功能提供负载均衡支持
http {
#负载均衡配置
upstream test {
#upstream的负载均衡，weight是权重，可以根据机器配置定义权重。
#weigth参数表示权值，权值越高被分配到的几率越大。
server www.baidu.com weight=2;
server www.163.com weight=1;
server www.example7.com weight=1;
}
server {
listen 8888;
server_name localhost;
client_max_body_size 100m;
#反向代理
location / {
# root /usr/local/nginx/html/;
# proxy_pass http://172.20.106.204;
proxy_pass http://test;
# proxy_redirect off;
proxy_set_header Host www.example7.com;
# proxy_set_header X-Real-IP $remote_addr;
# proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
location /images/ {
root /usr/local/nginx/;#访问/usr/local/nginx/images/
}
location ~ \.(mp3|mp4) {
root /usr/local/nginx/media/;#访问/usr/local/nginx/media/
}
}
}
4.HTTP和Restful
4.1 Http
-
HTTP协议，即超文本传输协议（Hypertext transfer protocol）。是一种详细规定了浏览器和万维网（WWW = World Wide Web）服务器之间互相通信的规则，通过因特网传送万维网文档的数据传送协议，可以传输文本，图片，视频等。
-
HTTP协议作为TCP/IP模型中应用层的协议也不例外。HTTP 协议通常承载于TCP协议之 上，有时也承载于TLS或SSL协议层之上，这个时候，就成了我们常说的HTTPS。
-
HTTP默认的端口号为 80，HTTPS的端口号为443。
-
HTTP0.9和1.0使用非持续连接：限制每次连接只处理一个请求，服务器处理完客户的请求，并收到客户的应答后，即断开连接。HTTP1.1使用持续连接：不必为每个web对象创建一个新的连接，一个连接可以传送多个对象，采用这种方式可以节省传输时间。
-
HTTP是媒体独立的：这意味着，只要客户端和服务器知道如何处理的数据内容，任何类型的数据都可以通过HTTP发 送。客户端以及服务器指定使用适合的MIME-type内容类型。
-
HTTP是无状态：HTTP协议是无状态协议。无状态是指协议对于事务处理没有记忆能力。缺少状态意味着如果后续处 理需要前面的信息，则它必须重传，这样可能导致每次连接传送的数据量增大。另一方面，在服务器不需要先前信息时它的应答就较快。
-
HTTP1.0定义了三种请求方法: GET、POST和HEAD方法。
-
HTTP1.1新增了六种请求方法: OPTIONS、PUT、PATCH、DELETE、TRACE和CONNECT方法。
方法 描述 GET 请求指定的页面信息，并返回实体主体。 HEAD 类似于GET请求，只不过返回的响应中没有具体的内容，用于获取报头。 POST 向指定资源提交数据进行处理请求（例如提交表单或者上传文件）。 数据被包含在请求体中。POST请求可能会导致新的资源的建立和/或已有资源的修改。 PUT 从客户端向服务器传送的数据取代指定的文档的内容。 DELETE 请求服务器删除指定的页面。 CONNECT HTTP/1.1协议中预留给能够将连接改为管道方式的代理服务器。 OPTIONS 允许客户端查看服务器的性能。 TRACE 回显服务器收到的请求，主要用于测试或诊断。 PATCH 是对 PUT 方法的补充，用来对已知资源进行局部更新。 -
HTTP响应头信息
应答头 说明 Allow 服务器支持哪些请求方法（如GET、POST等）。 Content-Encoding 文档的编码（Encode）方法。只有在解码之后才可以得到Content-Type头指定的内容类型。利用gzip压缩文档能够显著地减少HTML文档的下载时间。 Content-Length 表示内容长度。 Content-Type 表示后面的文档属于什么MIME类型。Servlet默认为text/plain，但通常需要显式 地指定为text/html。 Location 表示客户应当到哪里去提取文档。 Set-Cookie 设置和页面关联的Cookie。
4.2 Restful
- REST全称是Representational State Transfer，中文意思是表述（编者注：通常译为表征）性状态转移。 它首次出现在2000年Roy Fielding的博士论文中，Roy Fielding是HTTP规范的主要编写者之一。 他在论文中提到：“我这篇文章的写作目的，就是想在符合架构原理的前提下，理解和评估以网络为基础的应用软件的架构设计，得到一个功能强、性能好、适宜通信的架构。REST指的是一组架构约束条件和原则。” 如果一个架构符合REST的约束条件和原则，我们就称它为RESTful架构。
- REST本身并没有创造新的技术、组件或服务，而隐藏在RESTful背后的理念就是使用Web的现有特征和能力，更好地使用现有Web标准中的一些准则和约束。虽然REST本身受Web技术的影响很深，但是理论上REST架构风格并不是绑定在HTTP上，只不过目前HTTP是唯一与REST相关的实例。所以我们这里描述的REST也是通过HTTP实现的REST。
- RESTful架构应该遵循统一接口原则，统一接口包含了一组受限的预定义的操作，不论什么样的资源，都是通过使用相同的接口进行资源的访问。接口应该使用标准的HTTP方法如GET，PUT和POST，并遵循这些方法的语义。
- 如果按照HTTP方法的语义来暴露资源，那么接口将会拥有安全性和幂等性的特性，例如GET和HEAD请求都是安全的，无论请求多少次，都不会改变服务器状态。而GET、HEAD、PUT和DELETE请求都是幂等的，无论对资源操作多少次，结果总是一样的，后面的请求并不会产生比第一次更多的影响。
5.Redis操作和编程
5.1 Redis操作
-
REmote DIctionary Server（Redis）是一个由Salvatore Sanfilippo写的key-value存储系统。Redis是一个开源的使用ANSI C语言编写、遵守 BSD 协议、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库，并提供多种语言的API。它通常被称为数据结构服务器，因为值（value）可以是字符串(String)、哈希(Hash)、列表(list)、集合(sets)和有序集合(sorted sets)等类型。
-
编译安装
# 下载源码 wget http://download.redis.io/releases/redis-6.0.0.tar.gz tar zxvf redis-6.0.0.tar.gz # 编译 cd redis-6.0.0/ make -j 8 # 安装（默认安装到/usr/local/bin/目录） sudo make install -
常用命令
-
查看版本
redis-server -v -
启动
redis-server redis.conf
-
-
redis-cli
-
连接
redis-cli -h 127.0.0.1 -p 6379 -a 123456 -
停止服务
redis-cli -h 127.0.0.1 -p 6379 shutdown -
内部命令
命令 说明 DEL key 该命令用于在key存在时删除 key。 DUMP key 序列化给定key，并返回被序列化的值。 EXISTS key 检查给定key是否存在。 EXPIRE key seconds 为给定key设置过期时间，以秒计。 EXPIREAT key timestamp EXPIREAT的作用和EXPIRE类似，都用于为key设置过期时间。不同在于EXPIREAT命令接受的时间参数是UNIX时间戳(unix timestamp)。 PEXPIRE key milliseconds 设置key的过期时间以毫秒计。 PEXPIREAT key milliseconds-timestamp 设置key过期时间的时间戳(unix timestamp) 以毫秒计。 KEYS pattern 查找所有符合给定模式( pattern)的key。 MOVE key db 将当前数据库的key移动到给定的数据库db当中。 PERSIST key 移除key的过期时间，key将持久保持。 PTTL key 以毫秒为单位返回key的剩余的过期时间。 TTL key 以秒为单位，返回给定key的剩余生存时间(TTL, time to live)。 RANDOMKEY 从当前数据库中随机返回一个key。 RENAME key newkey 修改key的名称。 RENAMENX key newkey 仅当newkey不存在时，将key改名为newkey。 TYPE key 返回key所储存的值的类型。
-
-
redis基本数据结构
5.2 hiredis编程
-
编译安装hiredis
cd deps/hiredis make -j 8 sudo make install sudo ldconfig -
// 连接Redis服务 redisContext *context = redisConnect("127.0.0.1", 6379); if (context == NULL || context->err) { if (context) { printf("%s\n", context->errstr); } else { printf("redisConnect error\n"); } exit(EXIT_FAILURE); } printf("-----------------connect success--------------------\n"); // REDIS_REPLY_STRING == 1 :返回值是字符串,字符串储存在redis->str当中,字符串长度为redis->len。 // REDIS_REPLY_ARRAY == 2 :返回值是数组,数组大小存在redis->elements里面,数组值存储在redis->element[i]里面。数组里面存储的是指向redisReply的指针,数组里面的返回值可以通过redis->element[i]->str来访问,数组的结果里全是type==REDIS_REPLY_STRING的redisReply对象指针。 // REDIS_REPLY_INTEGER == 3 :返回值为整数long long。 // REDIS_REPLY_NIL == 4 :返回值为空表示执行结果为空。 // REDIS_REPLY_STATUS == 5 :返回命令执行的状态,比如set foo bar返回的状态为OK,存储在str当中reply->str == "OK"。 // REDIS_REPLY_ERROR == 6 :命令执行错误,错误信息存放在reply->str当中。 // 授权 redisReply *reply = redisCommand(context, "auth gongluck"); printf("type : %d\n", reply->type); if (reply->type == REDIS_REPLY_STATUS) { printf("auth ok\n"); } else if (reply->type == REDIS_REPLY_ERROR) { printf("auth err : %s\n", reply->str); } freeReplyObject(reply); // Set Key Value char *key = "str"; char *val = "Hello World"; reply = redisCommand(context, "SET %s %s", key, val); printf("type : %d\n", reply->type); if (reply->type == REDIS_REPLY_STATUS) { printf("SET %s %s\n", key, val); } freeReplyObject(reply); // GET Key reply = redisCommand(context, "GET %s", key); if (reply->type == REDIS_REPLY_STRING) { printf("GET str %s\n", reply->str); printf("GET len %ld\n", reply->len); } freeReplyObject(reply); // APPEND key value char *append = " I am your GOD"; reply = redisCommand(context, "APPEND %s %s", key, append); if (reply->type == REDIS_REPLY_INTEGER) { printf("APPEND %s %s \n", key, append); } freeReplyObject(reply); reply = redisCommand(context, "GET %s", key); if (reply->type == REDIS_REPLY_STRING) { printf("GET %s\n", reply->str); } freeReplyObject(reply); // INCR key reply = redisCommand(context, "INCR counter"); if (reply->type == REDIS_REPLY_INTEGER) { printf("INCR counter %lld\n", reply->integer); } freeReplyObject(reply); reply = redisCommand(context, "INCR counter"); if (reply->type == REDIS_REPLY_INTEGER) { printf("INCR counter %lld\n", reply->integer); } freeReplyObject(reply); // DECR key reply = redisCommand(context, "DECR counter"); if (reply->type == REDIS_REPLY_INTEGER) { printf("DECR counter %lld\n", reply->integer); } freeReplyObject(reply); reply = redisCommand(context, "DECR counter"); if (reply->type == REDIS_REPLY_INTEGER) { printf("DECR counter %lld\n", reply->integer); } freeReplyObject(reply); // DECRBY key decrement reply = redisCommand(context, "DECRBY counter 5"); if (reply->type == REDIS_REPLY_INTEGER) { printf("DECRBY counter %lld\n", reply->integer); } freeReplyObject(reply); reply = redisCommand(context, "DECRBY counter 5"); if (reply->type == REDIS_REPLY_INTEGER) { printf("DECRBY counter %lld\n", reply->integer); } freeReplyObject(reply); // INCRBY key increment reply = redisCommand(context, "INCRBY counter 5"); if (reply->type == REDIS_REPLY_INTEGER) { printf("INCRBY counter %lld\n", reply->integer); } freeReplyObject(reply); reply = redisCommand(context, "INCRBY counter 5"); if (reply->type == REDIS_REPLY_INTEGER) { printf("INCRBY counter %lld\n", reply->integer); } freeReplyObject(reply); // GETRANGE key start end reply = redisCommand(context, "GETRANGE str 0 5"); if (reply->type == REDIS_REPLY_STRING) { printf("GETRANGE %s %s\n", key, reply->str); } freeReplyObject(reply); // GETSET key value reply = redisCommand(context, "GETSET %s %s", key, val); if (reply->type == REDIS_REPLY_STRING) { printf("GETSET %s %s\n", key, reply->str); } freeReplyObject(reply); /*INCRBYFLOAT key increment*/ reply = redisCommand(context, "INCRBYFLOAT f 2.1"); if (reply->type == REDIS_REPLY_STRING) { printf("INCRBYFLOAT counter %s\n", reply->str); } freeReplyObject(reply); /*MSET key value [key value ...]*/ reply = redisCommand(context, "MSET k1 hello k2 world k3 good"); if (reply->type == REDIS_REPLY_STATUS) { printf("MSET k1 hello k2 world k3 good\n"); } freeReplyObject(reply); /*MGET key [key ...]*/ reply = redisCommand(context, "MGET k1 k2 k3"); if (reply->type == REDIS_REPLY_ARRAY) { printf("MGET k1 k2 k3 \n"); redisReply **pReply = reply->element; int i = 0; size_t len = reply->elements; //hello world good for (; i < len; ++i) { printf("%s ", pReply[i]->str); } printf("\n"); } freeReplyObject(reply); /*STRLEN key*/ reply = redisCommand(context, "STRLEN str"); if (reply->type == REDIS_REPLY_INTEGER) { printf("STRLEN str %lld \n", reply->integer); } freeReplyObject(reply); /*SETEX key seconds value*/ reply = redisCommand(context, "SETEX s 10 10seconds"); if (reply->type == REDIS_REPLY_STATUS) { printf("SETEX s 10 10seconds\n"); freeReplyObject(reply); int i = 0; while (i++ < 12) { reply = redisCommand(context, "GET s"); if (reply->type == REDIS_REPLY_STRING) { printf("%d s %s\n", i, reply->str); } else if (reply->type == REDIS_REPLY_NIL) { printf("%d s nil\n", i); } freeReplyObject(reply); sleep(1); } } redisFree(context); return EXIT_SUCCESS;
6.MongoDB操作和编程
6.1 MongoDB操作
-
MongoDB概念
SQL术语/概念 MongoDB术语/概念 解释/说明 database database 数据库 table collection 数据库表/集合 row document 数据记录行/文档 column field 数据字段/域 index index 索引 table joins - 表连接，MongoDB不支持 primary key primary key 主键，MongoDB自动将_id字段设置为主键 -
安装MongoDB
wget https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-ubuntu1804-4.4.1.tgz tar -zxvf mongodb-linux-x86_64-ubuntu1804-4.4.1.tgz sudo mv mongodb-linux-x86_64-ubuntu1804-4.4.1 /usr/local/mongodb -
启动MongoDB
-
MongoDB的数据存储在data目录的db目录下，但是这个目录在安装过程不会自动创建，所以需要手动创建data目录，并在data目录中创建db目录。
-
可以在命令行中执行mongo安装目录中的bin目录执行mongod命令来启动mongdb服务。如果数据库目录不是/data/db，可以通过–dbpath来指定。
/usr/local/mongodb/bin/mongod --dbpath=/data/db --bind_ip=0.0.0.0
-
-
MongoDB后台管理
-
MongoDB Shell是MongoDB自带的交互式Javascript shell，用来对MongoDB进行操作和管理的交互式环境。它默认会链接到test文档（数据库）
/usr/local/mongodb/bin/mongo -
显示所有数据的列表
show dbs -
显示当前数据库对象或集合
db -
（创建）连接到一个指定的数据库。
use gongluck -
插入数据
db.gongluck.insert({"name":"gongluck"}) -
删除当前数据库
db.dropDatabase() -
创建集合
db.createCollection("test") -
查看已有集合
show collections -
删除集合
db.test.drop() -
插入文档
db.test.insert({"name":"gongluck","socre":100}) -
查看已插入文档
db.test.find().pretty() -
更新文档
db.test.update({"name":"gongluck"}, {$set:{"name":"updated"}}, {multi:true}) db.test.save({"_id":ObjectId("5fafd5407f51c6334abca881"),"name":"gongluck","socre":100}) -
删除文档
db.test.remove({"name":"gongluck"}) db.test.remove() -
创建索引
# 1:升序,-1:降序 db.test.createIndex({"name":1,"score":-1})
-
6.2 MongoDB编程
-
编译mongo-c-driver
wget https://github.com/mongodb/mongo-c-driver/releases/download/1.17.2/mongo-c-driver-1.17.2.tar.gz tar -zxvf mongo-c-driver-1.17.2.tar.gz mongo-c-driver-1.17.2/ mkdir cmake-build cd cmake-build cmake -DENABLE_AUTOMATIC_INIT_AND_CLEANUP=OFF .. cmake --build . sudo cmake --build . --target install -
//gcc mongodb.c -I/usr/local/include/libmongoc-1.0 -I/usr/local/include/libbson-1.0/ -lmongoc-1.0 -lbson-1.0 #include <bson.h> #include <mongoc.h> int main() { mongoc_client_t *client; mongoc_collection_t *collection; bson_t *insert; bson_error_t error; //初始化libmongoc驱动 mongoc_init(); //创建连接对象 client = mongoc_client_new("mongodb://localhost:27017"); //获取指定数据库和集合 collection = mongoc_client_get_collection(client, "gongluck", "test"); //字段为hello，值为world字符串 insert = BCON_NEW("hello", BCON_UTF8("world")); //插入文档 if (!mongoc_collection_insert(collection, MONGOC_INSERT_NONE, insert, NULL, &error)) { fprintf(stderr, "%s\n", error.message); } bson_destroy(insert); mongoc_collection_destroy(collection); //释放表对象 mongoc_client_destroy(client); //释放连接对象 mongoc_cleanup(); //释放libmongoc驱动 return 0; }
Last modified on 2020-11-30