As you say you want to create view with dynamic database names - so the result you want to achieve is not possible in current versions of mysql.
So you have example following options:
Option 1
If you want to get result of all databases users tables you could define a stored procedure that uses prepared statement. This procedure needs parameter db_prefix what in your case is company_%. Basicly this procedure selects all tables named as users from information_schema when database name is like db_prefix parameter value. After that it loops through results and creates query string as union all users tables and executes this query. When creating a query string i also add field called source, so i can identify from what database this result is coming. In my example my databases are all in default collation utf8_unicode_ci.
In this case you can define procedure example "getAllUsers"
-- Dumping structure for procedure company_abc1.getAllUsers
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUsers`(IN `db_prefix` TEXT)
DETERMINISTIC
COMMENT 'test'
BEGIN
DECLARE qStr TEXT DEFAULT '';
DECLARE cursor_VAL VARCHAR(255) DEFAULT '';
DECLARE done INTEGER DEFAULT 0;
DECLARE cursor_i CURSOR FOR SELECT DISTINCT (table_schema) FROM information_schema.tables WHERE table_name = 'users' AND table_schema LIKE db_prefix COLLATE utf8_unicode_ci;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor_i;
read_loop: LOOP
FETCH cursor_i INTO cursor_VAL;
IF done = 1 THEN
LEAVE read_loop;
END IF;
IF qStr != '' THEN
SET qStr = CONCAT(qStr, ' UNION ALL ');
END IF;
SET qStr = CONCAT(qStr, ' SELECT *, \'', cursor_VAL ,'\' as source FROM ', cursor_VAL, '.users');
END LOOP;
CLOSE cursor_i;
SET @qStr = qStr;
PREPARE stmt FROM @qStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @qStr = NULL;
END//
DELIMITER ;
Now you can get your all users result as:
CALL getAllUsers('company_%');
In my example database it results as:
id name source
1 User 1 company_abc1
2 User 2 company_abc1
3 User 3 company_abc1
1 User 1 company_abc2
2 User 2 company_abc2
3 User 3 company_abc2
1 User 1 company_abc3
2 User 2 company_abc3
3 User 3 company_abc3
1 User 1 company_abc4
2 User 2 company_abc4
3 User 3 company_abc4
1 User 1 company_abc5
2 User 2 company_abc5
3 User 3 company_abc5
Option 2
If you really, really need view then you can modify first procedure and instead of executeing select you can create view. Example like this:
-- Dumping structure for procedure company_abc1.createAllUsersView
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `createAllUsersView`(IN `db_prefix` TEXT)
DETERMINISTIC
COMMENT 'test'
BEGIN
DECLARE qStr TEXT DEFAULT '';
DECLARE cursor_VAL VARCHAR(255) DEFAULT '';
DECLARE done INTEGER DEFAULT 0;
DECLARE cursor_i CURSOR FOR SELECT DISTINCT (table_schema) FROM information_schema.tables WHERE table_name = 'users' AND table_schema LIKE db_prefix COLLATE utf8_unicode_ci;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor_i;
read_loop: LOOP
FETCH cursor_i INTO cursor_VAL;
IF done = 1 THEN
LEAVE read_loop;
END IF;
IF qStr != '' THEN
SET qStr = CONCAT(qStr, ' UNION ALL ');
END IF;
SET qStr = CONCAT(qStr, ' SELECT *, \'', cursor_VAL ,'\' as source FROM ', cursor_VAL, '.users');
END LOOP;
CLOSE cursor_i;
SET @qStr = CONCAT('CREATE OR REPLACE VIEW allUsersView AS ', qStr);
PREPARE stmt FROM @qStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @qStr = NULL;
END//
DELIMITER ;
In this stored procedure we create/replace view called allUsersView, so basicly every time you will execute this procedure it will updates view.
In my test case it creates view like this:
CREATE OR REPLACE VIEW `allusersview` AS
SELECT *, 'company_abc1' as source FROM company_abc1.users
UNION ALL SELECT *, 'company_abc2' as source FROM company_abc2.users
UNION ALL SELECT *, 'company_abc3' as source FROM company_abc3.users
UNION ALL SELECT *, 'company_abc4' as source FROM company_abc4.users
UNION ALL SELECT *, 'company_abc5' as source FROM company_abc5.users ;
And now you can use view.
SELECT * FROM allusersview
And result is same as in first option.
All tested on:
Mysql 5.6.16
