MySQL环境变量配置

1.Linux下比较简单，在/etc/my.cnf中修改就可以了

2.Windows下可以修改my.ini，但位置比较难找。

MySQL  bugs里有人这么回答：

Alright... Using the newest MySQL installer (5.6.11), the following command line parameter is automatically added to the service and the command line client start menu shortcut:
--defaults-file=C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

But if I try to manually use any of the mysql binary tools (mysqlcheck.exe, mysqldump.exe, ...), they still try to load the my.ini from:

"Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf c:\Program Files\MySQL\M
ySQL Server 5.6\my.ini c:\Program Files\MySQL\MySQL Server 5.6\my.cnf"

So Maybe it's time to add ProgramData\MySQL to the list? :) (or even give it precedence over all others)

这里有一个方法

Win+R 运行 "services.msc"

找到mysql的服务，双击打开，可以看到执行参数

 

3. 还有一种方法，在控制台执行：set global max_allowed_packet=100000000;

 

重启动mysql丢失，注意要启动新的命令行才能生效。

 

 

4. mysql 自定义函数，出现

Error Code: 1366 Incorrect string value: '\xE4\xBF\xA1\xE6\x81\xAF...

除了返回字段utf8外，还需要declare的字段也要是utf8才行

-- --------------------------------------------------------------------------------
-- get the full department path
-- Author:zhouyu
-- Data:2014.1.9
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_getFullDeptPath`
(in_dept_id varchar(32)) RETURNS varchar(800) CHARSET utf8
BEGIN
	declare deptPath varchar(800) charset utf8;
	declare deptName varchar(200) charset utf8;
	declare pDeptId varchar(32);
	set deptPath = '';
	-- 不支持递归，往下写4级吧
	-- 第一层
	select department_name,parent_department_id into deptName, pDeptId from tb_c_department where 
	department_id=in_dept_id;
	if pDeptId = '0' then
		set deptPath = deptName;
	else
		-- 第二层
		set deptPath = deptName;
		select department_name,parent_department_id into deptName, pDeptId from tb_c_department where 
		department_id=pDeptId;
		if pDeptId = '0' then
			set deptPath = concat(deptName,'-',deptPath);
		else
			-- 第三层
			set deptPath = concat(deptName,'-',deptPath);
			select department_name,parent_department_id into deptName, pDeptId from tb_c_department where 
			department_id=pDeptId;
			if pDeptId = '0' then
				set deptPath = concat(deptName,'-',deptPath);
			else
				-- 第四层
				set deptPath = concat(deptName,'-',deptPath);
				select department_name,parent_department_id into deptName, pDeptId from tb_c_department where 
				department_id=pDeptId;
				if pDeptId = '0' then
					set deptPath = concat(deptName,'-',deptPath);
				end if;
			end if;
		end if;
	end if;
	return deptPath;
END