MySQL：触发器将每个单词的首字母大写

So here it is guys,

所以这里是伙计们，

I have a table containing this :

我有一个包含这个的表：

CREATE TABLE IF NOT EXISTS `people` (
`Id_People` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Firstname` varchar(20) NOT NULL,
`Lastname` varchar(20) NOT NULL,
PRIMARY KEY (`Id_People`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1002 ;

How can I set a trigger that will capitalize the first letter of my Firstname and Lastname?

如何设置触发器以使我的名字和姓氏的第一个字母大写？

Whenever I add someone,

每当我加一个人，

On the insert :

在插入：

INSERT INTO people (Firstname, Lastname) VALUES ('doe', 'john')

Whenever I edit someone,

每当我编辑某人时，

And on the update :

并在更新：

UPDATE people SET Lastname = 'smith', Firstname= 'jane' WHERE Id_People = 1;

And this even with a double-barrelled name such as : john smith (firstname) doe (lastname)?

这甚至有一个双管名称，例如：john smith（名字）doe（姓氏）？

1 个解决方案

#1

I can suggest that you are using MySql RDBMS.

我可以建议您使用MySql RDBMS。

MySql doesnt provide standart function for capitalize first letter in each word.

MySql不提供标准函数来大写每个单词的首字母。

Firstly,

首先，

Create this function:

创建此功能：

DELIMITER $$
CREATE FUNCTION `capitalize`(s varchar(255)) RETURNS varchar(255) 
BEGIN
  declare c int;
  declare x varchar(255);
  declare y varchar(255);
  declare z varchar(255);

  set x = UPPER( SUBSTRING( s, 1, 1));
  set y = SUBSTR( s, 2);
  set c = instr( y, ' ');

  while c > 0
    do
      set z = SUBSTR( y, 1, c);
      set x = CONCAT( x, z);
      set z = UPPER( SUBSTR( y, c+1, 1));
      set x = CONCAT( x, z);
      set y = SUBSTR( y, c+2);
      set c = INSTR( y, ' ');     
  end while;
  set x = CONCAT(x, y);
  return x;
END$$

Secondly,

其次，

Create triggers for update and insert

创建更新和插入的触发器

CREATE TRIGGER frst_l_up_insert BEFORE INSERT ON people FOR EACH ROW
SET NEW.Firstname = capitalize(NEW.Firstname), NEW.Lastname = capitalize(NEW.Lastname);

CREATE TRIGGER frst_l_up_update BEFORE UPDATE ON people FOR EACH ROW
SET NEW.Firstname = capitalize(NEW.Firstname), NEW.Lastname = capitalize(NEW.Lastname)

Finally,

最后，

Update existings record in the table

更新表中的存在记录

UPDATE people SET Lastname = capitalize(Lastname), Firstname = capitalize(Firstname);

#1