GVKun编程网logo

【MySQL】实现自增函数sequence（mysql 自增函数）

1

本文将介绍【MySQL】实现自增函数sequence的详细情况，特别是关于mysql自增函数的相关信息。我们将通过案例分析、数据研究等多种方式，帮助您更全面地了解这个主题，同时也将涉及一些关于Acce

本文将介绍【MySQL】实现自增函数sequence的详细情况，特别是关于mysql 自增函数的相关信息。我们将通过案例分析、数据研究等多种方式，帮助您更全面地了解这个主题，同时也将涉及一些关于Access MySQL from C （用 C 访问 MySQL 数据库）、Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’的知识。

本文目录一览：

【MySQL】实现自增函数sequence（mysql 自增函数）

【MySQL】实现自增函数sequence（mysql 自增函数）

前言

当前数据库为：mysql
由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能，理由sql语句如下：

步骤

1.创建sequence表

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT ''序列的名字'',
  `current_value` int(11) NOT NULL COMMENT ''序列的当前值'',
  `increment` int(11) NOT NULL DEFAULT ''1'' COMMENT ''序列的自增值'',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.创建取当前值的函数

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''''
BEGIN
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence
          WHERE name = seq_name; 
     RETURN value; 
END
$ 
DELIMITER ;

3.创建取下一个值函数

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''''
BEGIN
     UPDATE sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ;

4.创建更新当前值函数

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '''' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

数据库结构

 

 调用

INSERT INTO sequence VALUES (''testSeq'', 0, 1);--添加一个sequence名称和初始值，以及自增幅度

SELECT SETVAL(''testSeq'', 10);--设置指定sequence的初始值

SELECT CURRVAL(''testSeq'');--查询指定sequence的当前值

SELECT NEXTVAL(''testSeq'');--查询指定sequence的下一个值

Access MySQL from C （用 C 访问 MySQL 数据库）

Access MySQL from C （用 C 访问 MySQL 数据库）

This blog is mainly a collection of study notes and some simple tryout examples. For more details, refer to "Beginning Linux Programming", Chapter 8.

 

Most Commonly Used APIs for Accessing MySQL:

MYSQL *mysql_init(MYSQL *);

MYSQL *mysql_real_connect(MYSQL *connection,

const char *server_host,

const char *sql_user_name,

const char *sql_password,

const char *db_name,

unsigned int port_number,

const char *unix_socket_name,

unsigned int flags);

void mysql_close(MYSQL *connection);

int mysql_options(MYSQL *connection, enum option_to_set,

const char *argument);

int mysql_query(MYSQL *connection, const char *query);

my_ulonglong mysql_affected_rows(MYSQL *connection);

unsigned int mysql_errno(MYSQL *connection);

char *mysql_error(MYSQL *connection);

MYSQL_RES *mysql_store_result(MYSQL *connection);

my_ulonglong mysql_num_rows(MYSQL_RES *result);

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);	

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);

void mysql_free_result(MYSQL_RES *result);

unsigned int mysql_field_count(MYSQL *connection);

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);

char *mysql_get_client_info(void);

char *mysql_get_host_info(MYSQL *connection);

char *mysql_get_server_info(MYSQL *connection);

char *mysql_info(MYSQL *connection);

int mysql_select_db(MYSQL *connection, const char *dbname);

int mysql_shutdown(MYSQL *connection, enum mysql_enum_shutdown_level);

 

Example1: how to connect to a mysql server

#include <stdlib.h>
#include <stdio.h>
#include "mysql.h"

int main(int argc, char *argv[])
{
        MYSQL *conn_ptr;

        conn_ptr = mysql_init(NULL);
	if (!conn_ptr)
	{
                fprintf(stderr, "mysql_init failed\n");
                exit(EXIT_FAILURE);
        }

        conn_ptr = mysql_real_connect(conn_ptr, "localhost", "chenqi", "helloworld",
                                      "test", 0, NULL, 0);

	if (conn_ptr)
	{
         	printf("Connection Success \n");
	}
        else
	{
         	printf("Connection failed \n");
	}

        mysql_close(conn_ptr);

	exit(EXIT_SUCCESS);
}

gcc -I/usr/include/mysql connect1.c -L/usr/lib/mysql -lmysqlclient -o connect1

Example2: how to handle errors

#include <stdlib.h>
#include <stdio.h>
#include "mysql.h"

int main(int argc, char *argv[])
{
        MYSQL conn;
        mysql_init(&conn);
        if (mysql_real_connect(&conn, "localhost", "chenqi",
	                       "i do not know", "test", 0, NULL, 0))
	{
                printf("Connection Success \n");
                mysql_close(&conn);
        }
        else
        {
                fprintf(stderr, "Connection Failed \n");
                if (mysql_errno(&conn))
                {
                        fprintf(stderr, "Connection error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }
        }
        exit(EXIT_SUCCESS);
}

result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./connect2
Connection Failed
Connection error 1045: Access denied for user ''chenqi''@''localhost'' (using password: YES)

Example3: how to insert and update

/* insert a row into the table children */
                ret = mysql_query(&conn, "insert into children(fname, age) values(''James'', 23)");
                if (!ret)
                {
                        printf("Inserted %lu rows \n",
                               (unsigned long)mysql_affected_rows(&conn));
                }
                else
                {
                        fprintf(stderr, "Insert error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }
                /* update a row in the table children */
                ret = mysql_query(&conn, "update children set age = 24 where fname = ''James''");
                if (!ret)
                {
                        printf("Update %lu rows \n",
                               (unsigned long)mysql_affected_rows(&conn));
                }
                else
                {
                        fprintf(stderr, "Update error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }

result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./insert-update
Connection Success
Inserted 1 rows
Update 2 rows

Example4: how to retrieve data into C application

Step1: Issue the query (mysql_query)
Step2: Retrieve the data (mysql_store_result, mysql_use_result)
Step3: Process the data (mysql_fetch_row)
Step4: Tidy up if necessary (mysql_free_result)

For a large data set, mysql_use_result should be considered, because it uses less storage.

MySQL, like other SQL databases, gives back two sorts of data:
1. The retrieved information from the table, namely the column data
2. Data about the data, so-called metadata, such as column types and names

#include <stdlib.h>
#include <stdio.h>

#include "mysql.h"

MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

void display_row()
{
	unsigned int field_count = 0;
	while(field_count < mysql_field_count(&my_connection))
	{
		if (sqlrow[field_count])
			printf("%12s", sqlrow[field_count]);
		else
			printf("%12s", "NULL");
		field_count++;
	}
	printf("\n");
}

/* show metadata of each column */
void display_header()
{
	MYSQL_FIELD *field_ptr;

	printf("Column Details:\n");
	while((field_ptr = mysql_fetch_field(res_ptr)) != NULL)
	{
		printf("\t Name: %s\n", field_ptr->name);
		printf("\t Type: ");
		if (IS_NUM(field_ptr->type))
		{
			printf("Numeric Field\n");
		}
		else
		{
			switch(field_ptr->type)
			{
			case FIELD_TYPE_VAR_STRING:
				printf("VARCHAR\n");
				break;
			case FIELD_TYPE_LONG:
				printf("LONG\n");
				break;
			default:
				printf("Type is %d, check in mysql_com.h\n", field_ptr->type);
			}
		} /* else */
		printf("\t Max_width %ld\n", field_ptr->max_length);
		if (field_ptr->flags & AUTO_INCREMENT_FLAG)
		{
			printf("\t Auto increments\n");
		}
		printf("\n");
	}	  /* while */
}

int main(int argc, char *argv[])
{
	int ret;
	mysql_init(&my_connection);
	if (mysql_real_connect(&my_connection, "localhost", "chenqi",
			       "helloworld", "test", 0, NULL, 0))
	{
		printf("Conncetion Success\n");
		ret = mysql_query(&my_connection, "select * from children");
		if (ret)	/* error */
		{
			printf("select error: %s\n", mysql_error(&my_connection));
		}
		else		/* ok */
		{
			res_ptr = mysql_store_result(&my_connection); /* mysql_use_result for an alternative */
			if (res_ptr)
			{
				printf("Retrieved %lu rows \n", (unsigned long)mysql_num_rows(res_ptr));
				display_header();
				while (sqlrow = mysql_fetch_row(res_ptr))
				{
///					printf("Fetching data ... \n");
					display_row();
				}
				if (mysql_errno(&my_connection))
				{
					fprintf(stderr, "Retrieve error: %s\n", mysql_error(&my_connection));
				}
				mysql_free_result(res_ptr); /* res_ptr != NULL */
			}
		}
		mysql_close(&my_connection);
	}
	else
	{
		fprintf(stderr, "Connection Failed\n");
		if (mysql_errno(&my_connection))
		{
			fprintf(stderr, "Connection error %d: %s\n",
				mysql_errno(&my_connection), mysql_error(&my_connection));
		}
		exit(EXIT_FAILURE);
	}
	exit(EXIT_SUCCESS);
}

Result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./select1
Conncetion Success
Retrieved 12 rows
Column Details:
         Name: childno
         Type: Numeric Field
         Max_width 2
         Auto increments

         Name: fname
         Type: VARCHAR
         Max_width 8

         Name: age
         Type: Numeric Field
         Max_width 2

           1       Jenny          21
           2        Feby          25
           3    Chandler          12
           4      Monica          23
           5      Rachel          21
           6        Ross           2
           7         Joy          11
           8        Emma          11
           9       Gavin          14
          10      Andrew          21
          12       James          24
          13         Tom          13

Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

 

 

 

原因：系统盘满了

[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

解决：

删除大文件后，重启系统解决

 

 

[root@localhost mysql]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is not running.
ProFTPD is running.

 

df: 未处理文件系统
[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

 

 

[root@localhost ~]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is running.
ProFTPD is running.

 

 

 

转

xampp 无法启动mysql 找不到mysql.sock

  (2016-02-24 23:21:24)
转载▼
  分类： 技术
出现的问题：
如果xampp中的mysql启动不了，出现ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)报错，
停止xampp的时候报：
-bash-4.1# /opt/lampp/lampp stop
Stopping XAMPP for Linux 1.8.2-6...
XAMPP: Stopping Apache...ok.
XAMPP: Stopping MySQL...ok.
XAMPP: Stopping ProFTPD...kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
fail.

解决办法：
如果网上一些方法不好用的话，可以试试以下方法：
1. 确定系统盘是否满了
#df -h
2. 删除/opt/lampp目录中的pid文件（删掉后xampp重启时会重建，如果不放心，可以先备份lampp目录）
删除mysql相关缓存：
#rm -rf /opt/lampp/var/mysql/VM_*  
删除proftp相关缓存：
#rm -rf /opt/lampp/var/proftpd.pid
如果找不到pid文件，可以搜一下：
#find /opt/lampp -name ''*.pid''

 

Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

MySQL已经被我移到数据盘了，本地连接数据库会报错：Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

但是远程是可以连接的，my.cnf设置mysql的根目录也改成了数据盘的地址，还要在加上client的参数，设置如下：

[client]
socket = /home/data/mysql/mysql.sock

之后重启下mysql就可以了

CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

亲，是不是忘记了开MysqL服务，service MysqLd start

今天关于【MySQL】实现自增函数sequence和mysql 自增函数的分享就到这里，希望大家有所收获，若想了解更多关于Access MySQL from C （用 C 访问 MySQL 数据库）、Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’等相关知识，可以在本站进行查询。

本文标签：

上一篇jdbc-mysql 中文乱码解决（jdbc从数据库中读的中文乱码）

下一篇MySQL-快速入门（4）MySQL函数（mysql函数大全）

相关文章