ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Восстановить таблицы из information_schema
19 сообщений из 19, страница 1 из 1
Восстановить таблицы из information_schema
    #38409118
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
Все доброго времени суток.

Проблема в следующем - удалили базу и осталась только её структура в information_schema.

Задача - восстановить все таблицы и их "PRIMARY KEY", "KEY" с помощью information_schema.

Удалось найти только такой SELECT:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         ', ',
         CONCAT('DEFAULT ', QUOTE(COLUMN_DEFAULT), ',')),
      UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'


В MySQL мало понимаю и доработать запрос не под силу. Буду благодарен тому кто поможет доработать запрос (или php скрипт), чтобы он сразу выводил готовую таблицу на подобии:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE IF NOT EXISTS `users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
...
Рейтинг: 0 / 0
27.09.2013, 00:09:18
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38409186
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон, для одной таблицы - вот
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select concat(
 'create table '
 ,' таблица ('
 ,group_concat(column_definition order by rn separator ',')
 ,') engine='
 ,(select `engine` from information_schema.tables where TABLE_SCHEMA = 'база' AND TABLE_NAME = 'таблица')
 ,';'
)
from (
 SELECT ordinal_position as rn,CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         '',
         CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT))),
      ' ',UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'
) t0

а для всей базы дописывать влом :)
...
Рейтинг: 0 / 0
27.09.2013, 05:55:03
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38409201
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Держите :)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
SET @@session.group_concat_max_len = 100500; /*не меньше, чем предполагаемый размер итогового скрипта создания базы*/
set @_schema:='название_базы';

select group_concat(ctf order by tn separator ';\r\n\r\n') da_script
from (

 select 
  t_columns.tn
  ,concat('create table '
          ,' `',t_columns.tn,'` (\r\n '
          ,group_concat(t_columns.col_def order by rn separator '\r\n ,')
          ,if(isnull(t_pkeys.pkey),'',concat('\r\n ,PRIMARY KEY (',t_pkeys.pkey,')'))
          ,'\r\n) engine='
          ,t_engines.`engine`
 ) as ctf

 from (
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
 ) t_columns

 join (
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema
 ) t_engines on t_columns.tn=t_engines.tn

 left join (
  SELECT table_name tn
  ,group_concat(if(ifnull(column_key,'')<>'PRI'
                ,null
				,concat('`',column_name,'`')) separator ','
			   ) pkey
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
  group by tn
 ) t_pkeys on t_columns.tn=t_pkeys.tn

group by t_columns.tn

) t0

Ну а индексы уже как-нибудь сами...
...
Рейтинг: 0 / 0
27.09.2013, 07:31:49
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38409982
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglir, спасибо что помогаете.

Но, к сожалению, результат обработки первого запроса (отдает не полную таблицу):

Код: sql
1.
create table  таблица (   `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `membergroupids` char(250) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `username` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `password` char(32) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `passworddate` date NOT NULL DEFAULT '0000-00-00' ,   `email` char(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `styleid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `parentemail` char(50) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `homepage` char(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `icq` char(20) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `aim` char(20) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `yah) engine=MyISAM;



а второй запрос вообще не работает (session.group_concat_max_len менял, толку мало), выдает много цифр, если нажать "Полные тексты" то:
Код: sql
1.
NULL
...
Рейтинг: 0 / 0
27.09.2013, 15:04:03
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410103
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон, вы название базы хоть подставили какое надо? у меня и первый, и второй прекрасно на двух БД отработали... мускль 5.0.не_помню_сколько, а у вас какая версия? (впрочем, навряд ли это имеет отношение к делу)
...
Рейтинг: 0 / 0
27.09.2013, 16:08:13
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410112
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglir, конечно.
Версия MySQL-клиента: 5.0.22
...
Рейтинг: 0 / 0
27.09.2013, 16:10:51
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410122
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
Версия MySQL сервера: 5.0.18-nt-max
Выполняю через phpMyAdmin 3.4.10.1
...
Рейтинг: 0 / 0
27.09.2013, 16:16:12
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410136
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон, ну хз. Вот этот кусок
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema

тоже ничего не выдаёт?
PS. Ешкин кот, как форматирование сбилось-то...
...
Рейтинг: 0 / 0
27.09.2013, 16:21:16
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410151
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglir, все ок, вывело все таблицы и столбцы.

 
результат
...
Рейтинг: 0 / 0
27.09.2013, 16:30:52
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410259
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон,

ну а второй селект? тот, что к information_schema.tables?
...
Рейтинг: 0 / 0
27.09.2013, 17:46:29
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410286
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglirмаXон,

ну а второй селект? тот, что к information_schema.tables?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select concat(
 'create table '
 ,' таблица ('
 ,group_concat(column_definition order by rn separator ',')
 ,') engine='
 ,(select `engine` from information_schema.tables where TABLE_SCHEMA = 'база' AND TABLE_NAME = 'таблица')
 ,';'
)
from (
 SELECT ordinal_position as rn,CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         '',
         CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT))),
      ' ',UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'
) t0



Этот?

Он отдает не все поля:

Код: sql
1.
create table  таблица (   `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `membergroupids` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `username` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `password` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `passworddate` date NOT NULL DEFAULT '0000-00-00' ,   `email` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `styleid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `parentemail` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `homepage` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `icq` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `aim` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NUL) engine=MyISAM;
...
Рейтинг: 0 / 0
27.09.2013, 18:15:12
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410319
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
tanglir
Код: sql
1.
2.
3.
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema

этот
...
Рейтинг: 0 / 0
27.09.2013, 18:41:26
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410352
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglirtanglir
Код: sql
1.
2.
3.
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema

этот
Код: sql
1.
2.
3.
4.
5.
6.
7.
tn 	        engine
access 	        MyISAM
adminhelp 	MyISAM
administrator 	MyISAM
adminlog 	MyISAM
adminmessage 	MyISAM
adminutil 	MyISAM


и т.д.
В общем отдает все таблицы.
...
Рейтинг: 0 / 0
27.09.2013, 19:10:29
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410602
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон, ну тогда я не знаю. У меня всё работает. И судя по представленным вами данным, и у вас работать должно. Попробуйте ещё без этих двух строчек
Код: sql
1.
2.
select group_concat(ctf order by tn separator ';\r\n\r\n') da_script
from (

и без последней. Должно вывести список таблиц с криэйтами...
...
Рейтинг: 0 / 0
28.09.2013, 10:32:53
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410733
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglir,

запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
SET @@session.group_concat_max_len = 100500;
set @_schema:='база';


 select 
  t_columns.tn
  ,concat('create table '
          ,' `',t_columns.tn,'` (\r\n '
          ,group_concat(t_columns.col_def order by rn separator '\r\n ,')
          ,if(isnull(t_pkeys.pkey),'',concat('\r\n ,PRIMARY KEY (',t_pkeys.pkey,')'))
          ,'\r\n) engine='
          ,t_engines.`engine`
 ) as ctf

 from (
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
 ) t_columns

 join (
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema
 ) t_engines on t_columns.tn=t_engines.tn

 left join (
  SELECT table_name tn
  ,group_concat(if(ifnull(column_key,'')<>'PRI'
                ,null
				,concat('`',column_name,'`')) separator ','
			   ) pkey
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
  group by tn
 ) t_pkeys on t_columns.tn=t_pkeys.tn

group by t_columns.tn



 
ответ:
...
Рейтинг: 0 / 0
28.09.2013, 17:21:45
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410739
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон,
"63726561746520" - это "create " в хексах. Разбирайтесь, почему так происходит :)
...
Рейтинг: 0 / 0
28.09.2013, 17:47:30
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410743
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglir,
откуда мне знать. Может Вы что-то подскажете?
...
Рейтинг: 0 / 0
28.09.2013, 17:53:57
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410761
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
маXон, определённо сказать невозможно. ХШ говорит, что пхпадмин может чудить. Попробуйте выполнить запрос из клиента mysql. Или из devart dbforge, или из heidisql...
...
Рейтинг: 0 / 0
28.09.2013, 18:16:21
| Ответить | Цитировать | Написать  
Восстановить таблицы из information_schema
    #38410894
маXон
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
маXон
Гость
tanglirмаXон, определённо сказать невозможно. ХШ говорит, что пхпадмин может чудить. Попробуйте выполнить запрос из клиента mysql. Или из devart dbforge, или из heidisql...Действительно, взял клиент mysql и получил нормальный вывод данных.
Большое спасибо за помощь.
...
Рейтинг: 0 / 0
28.09.2013, 23:38:53
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Восстановить таблицы из information_schema
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38409982&tid=1835961]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 206ms
total:  342ms
созд. / загр.: 342ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]